Fees by payment method

1-- core.adyen.fees_by_payment_method
2-- What each payment method costs: settled volume, refunds, chargebacks and the
3-- transaction fees withheld, with interchange, scheme fees and markup broken out
4-- where Adyen has interchange level data.
5--
6-- Grain: day (creation date, report time zone) x merchant account x payment method
7-- x payment method variant x net (settlement) currency.
8-- Depends on: staging.adyen.settlement_detail
9--
10-- Only transaction entries (settled, refunded, charged back) are included. Batch
11-- level entries such as Adyen's own Fee lines, invoice deductions and payouts carry
12-- no payment method and live in settlement_batches.
13--
14-- AMOUNTS are in the net currency, positive for money in and negative for money
15-- out, except fee columns, where positive is a cost. settled_gross is the settled
16-- amount before fees: net credit plus the fees withheld from it.
17--
18-- RATES. effective_fee_rate is a ratio of sums for the row. To get a rate over a
19-- month or a payment method, re-aggregate transaction_fees and settled_gross and
20-- divide; never average the daily rates.
21
22SELECT
23    s.created_date_local                                                          AS date,
24    s.merchant_account,
25    s.payment_method,
26    s.payment_method_variant,
27    s.net_currency,
28
29    COUNTIF(s.entry_category = 'settled')                                         AS settled_count,
30    SUM(CASE WHEN s.entry_category = 'settled' THEN s.net_amount_nc + s.transaction_fees_nc ELSE 0 END) AS settled_gross,
31    SUM(CASE WHEN s.entry_category = 'settled'    THEN s.net_amount_nc ELSE 0 END) AS settled_net,
32    COUNTIF(s.entry_category = 'refund')                                          AS refund_count,
33    SUM(CASE WHEN s.entry_category = 'refund'     THEN s.net_amount_nc ELSE 0 END) AS refunds,
34    COUNTIF(s.entry_category = 'chargeback')                                      AS chargeback_count,
35    SUM(CASE WHEN s.entry_category = 'chargeback' THEN s.net_amount_nc ELSE 0 END) AS chargebacks,
36
37    SUM(s.transaction_fees_nc)                                                    AS transaction_fees,
38    SUM(COALESCE(s.interchange_nc, 0))                                            AS interchange,
39    SUM(COALESCE(s.scheme_fees_nc, 0))                                            AS scheme_fees,
40    SUM(COALESCE(s.markup_nc, 0))                                                 AS markup,