Skip to content

1205. Monthly Transactions II 👎

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
WITH
  ApprovedTransactionsAndChargebacks AS (
    SELECT
      id,
      country,
      state,
      amount,
      DATE_FORMAT(trans_date, '%Y-%m') AS `month`
    FROM Transactions
    WHERE state = 'approved'
    UNION ALL
    SELECT
      trans_id AS id,
      country,
      'chargeback' AS state,
      amount,
      DATE_FORMAT(Chargebacks.trans_date, '%Y-%m') AS `month`
    FROM Chargebacks
    LEFT JOIN Transactions
      ON (Chargebacks.trans_id = Transactions.id)
  )
SELECT
  `month`,
  country,
  SUM(state = 'approved') AS approved_count,
  SUM(IF(state = 'approved', amount, 0)) AS approved_amount,
  SUM(state = 'chargeback') AS chargeback_count,
  SUM(IF(state = 'chargeback', amount, 0)) AS chargeback_amount
FROM ApprovedTransactionsAndChargebacks
GROUP BY 1, 2;