Stripe Sigma: Year revenue by country

readme.sql

-- breakdown revenue per country
-- we store billing address only in the Charge object

-- NOTE this is rough and there are 2 different country columns to consider here

with 

-- transactions table is what we want since we can sum it all up and that will subtract out refunds
-- however we have to join to each Source separately so we can pull out the charge_id for all types of transactions
-- because the charge is what we need for the address, since it doesn't exist on the Customer, nor on the e.g. refund
transactions_with_charge_id as (
  select
    txn.*,
    COALESCE(charges.id, refunds.charge_id, disputes.charge_id) as charge_id
  from balance_transactions as txn
  left join charges on txn.source_id = charges.id
  left join refunds on txn.source_id = refunds.id
  left join disputes on txn.source_id = disputes.id
  where txn.type != 'payout'
    and txn.created >= date('2019-01-01')
    and txn.created < date('2020-01-01')
    order by amount asc
), 

-- now let's join that up with the Charges table so that even for e.g. a Refund source transaction,
-- we have the corresponding charge object, which contains the billing address
transactions_with_locations as (
  select 
    txn.id,
    txn.amount / 100.0 as amount_dollars,
    txn.type,
    txn.source_id,
    txn.charge_id,
    charges.card_address_state,
    charges.card_address_zip,
    charges.card_address_country,
    charges.card_country
  from transactions_with_charge_id as txn
  left join charges on txn.charge_id = charges.id
  where charges.captured
    and charges.status = 'succeeded'
    order by txn.amount
)

select 
  card_address_country,
  card_country,
  round(sum(amount_dollars)) as amount_dollars_sum
from transactions_with_locations as txn
    -- sometimes the card_country says 'US' but the card billing address is Canada etc.
    -- so we'll use card_address_country if it is exists, fallback to card_country
group by card_address_country, card_country
order by amount_dollars_sum desc