--This template returns an itemized list of disputed charges for the past 30 days,
--along with information such as whether evidence has been submitted and the status of the dispute
-- Note: if you have currencies that do not have cents (e.g. JPY), you should not divide by 100.0

select
  charges.id as charge_id,
  date_format(disputes.created, '%Y-%m-%d') as dispute_date,
  date_format(charges.created, '%Y-%m-%d') as charge_date,
  charges.card_brand,
  disputes.reason,
  disputes.status,
  disputes.amount / 100.00 as dispute_amount,
  disputes.currency,
  disputes.evidence_details_has_evidence as evidence_saved,
  case when disputes.evidence_details_submission_count = 0 then false else true end as evidence_submitted
from disputes
join charges
  on charges.id = disputes.charge_id
where date(charges.created) >= date_add('day', -30, current_date)
order by 2 desc
          
        

  


  

        
        
        
        -- This template returns a monthly summary of your activities, including all payments in the UTC time zone
-- Note: this query may need to be modified to handle additional payout/transfer types if you are using Connect

-- monthly_balance_transactions is a temporary table that aggregates and pivots different
-- balance_transaction types on a monthly basis for each currency
-- Note: only months with at least 1 balance transaction will be returned
with monthly_balance_transactions as (
  select
    date_trunc('month', case when type = 'payout' then available_on else created end) as month, -- payouts are considered when they are posted (available_on)
    currency,
    sum(case when type in ('charge', 'payment') then amount else 0 end) as sales,
    sum(case when type in ('payment_refund', 'refund') then amount else 0 end) as refunds,
    sum(case when type = 'adjustment' then amount else 0 end) as adjustments,
    sum(case when type not in ('charge', 'payment', 'payment_refund', 'refund', 'adjustment', 'payout') and type not like '%transfer%' then amount else 0 end) as other,
    sum(case when type <> 'payout' and type not like '%transfer%' then amount else 0 end) as gross_transactions,
    sum(case when type <> 'payout' and type not like '%transfer%' then net else 0 end) as net_transactions,
    sum(case when type = 'payout' or type like '%transfer%' then fee * -1.0 else 0 end) as payout_fees,
    sum(case when type = 'payout' or type like '%transfer%' then amount else 0 end) as gross_payouts,
    sum(case when type = 'payout' or type like '%transfer%' then fee * -1.0 else net end) as monthly_net_activity,
    count_if(type in ('payment', 'charge')) as sales_count,
    count_if(type = 'payout') as payouts_count,
    count(distinct case when type = 'adjustment' then source_id end) as adjustments_count
  from balance_transactions
  group by 1, 2
)

-- Compute the month_end_balance for each month and format output
-- Note: if you have currencies that do not have cents (e.g. JPY), you should not divide by 100.0
select
  date_format(month, '%Y-%m') as month,
  currency,
  sales / 100.0 as sales,
  refunds / 100.0 as refunds,
  adjustments/100.0 as adjustments,
  other / 100.0 as other,
  gross_transactions / 100.0 as gross_transactions,
  net_transactions / 100.0 as net_transactions,
  payout_fees / 100.0 as payout_fees,
  gross_payouts / 100.0 as gross_payouts,
  monthly_net_activity/100.0 as monthly_net_activity,
  sum(monthly_net_activity + gross_payouts) over(partition by currency order by month)/100.0 as month_end_balance, -- use SUM Window Function
  sales_count,
  payouts_count,
  adjustments_count
from monthly_balance_transactions
where month < date_trunc('month', current_date) -- exclude current, partial month
order by 1 desc, 2
          
        

  


  

        
        
        
        -- This template returns all unpaid invoices and relevant customer and plan information
-- Note: if you have currencies that do not have cents (e.g. JPY), you should not divide by 100.0

select
  date(invoices.date) as invoice_date,
  invoices.amount_due / 100.0 as amount_due,
  invoices.attempt_count,
  invoices.customer_id,
  customers.email,
  plans.name as plan_name
from invoices
join subscriptions
  on invoices.subscription_id = subscriptions.id
join plans -- join subscriptions on plans to get plan name
  on subscriptions.plan_id = plans.id
join customers -- join on customers to get customer email
  on invoices.customer_id = customers.id
where not invoices.paid -- filter out paid and forgiven invoices
  and invoices.status != 'uncollectible'
order by 2 desc, 1
          
        

  


  

        
        
        
        -- This template returns itemized information for transfers/payouts linked to
-- automatic transfers within the past 30 days

select
  date(transfers.date) as transfer_date,
  transfers.id as transfers_id,
  transfers.amount/100.0 as transfer_amount,
  transfers.status as transfers_status,
  balance_transactions.id as balance_transaction_id,
  balance_transactions.source_id as balance_transaction_source_id,
  balance_transactions.type as balance_transaction_type,
  balance_transactions.currency,
  balance_transactions.amount/100.0 as balance_transaction_amount,
  balance_transactions.net/100.0 as balance_transaction_net,
  charges.statement_descriptor as statement_descriptor,
  disputes.reason as disputes_reason,
  refunds.reason as refund_reason
from transfers
join balance_transactions
  on balance_transactions.automatic_transfer_id = transfers.id
left join charges
  on charges.id = balance_transactions.source_id -- balance_transactions.source_id can be used to join on charges, disputes, and refunds table.
left join disputes
  on disputes.id = balance_transactions.source_id
left join refunds
  on refunds.id = balance_transactions.source_id
where transfers.type = 'bank_account'
  and transfers.date >= date_add('day', -30, current_date)
order by transfers.date desc, transfers.id, balance_transactions.created desc
          
        

  


  

        
        
        
        -- This template returns gross processing volume for each currency per month, assuming a local timezone of PT
-- Note: charges can change over time, for example if a charge gets refunded.
-- Always use the balance transactions table if you need to create reports for accounting purposes

-- charges_timezone_conversion is a temporary table that converts timestamp with UTC timezone to 'America/Los_Angeles' timezone
with charges_timezone_conversion as (
  select
    date_trunc('month', created at time zone 'America/Los_Angeles') as month,
    currency,
    amount
  from charges
  where captured -- filter out uncaptured charges
)

-- Compute the monthly gross charges for each month and currency
select
  date_format(month, '%Y-%m') as month,
  currency,
  sum(amount)/100.0 as gross_charges
from charges_timezone_conversion
where month >= date_add('month', -24, date_trunc('month', current_timestamp at time zone 'America/Los_Angeles'))
group by 1, 2
order by 1 desc, 2
          
        

  


  

        
        
        
        -- This template returns the balance at the end of every day in the UTC time zone

-- daily_balance_transactions is a temporary table that aggregates and pivots different
-- balance_transaction types on a daily basis for each currency
with daily_balance_transactions as (
  select
    -- payouts are considered when they are posted (available_on)
    date(case when type = 'payout' then available_on else created end) as day,
    currency,
    sum(net) as daily_balance,
    sum(case when type = 'payout' then net else 0 end) as payouts,
    sum(case when type <> 'payout' then net else 0 end) as net_transactions,
    sum(case when type in ('charge', 'payment') then net else 0 end) as payments, -- net = amount - fee
    sum(case when type in ('payment_refund', 'refund', 'payment_failure_refund') then net else 0 end) as refunds,
    sum(case when type = 'transfer' then net else 0 end) as transfers,
    sum(case when type = 'adjustment' and lower(description) like 'chargeback withdrawal%' then net else 0 end) as chargeback_withdrawals,
    sum(case when type = 'adjustment' and lower(description) like 'chargeback reversal%' then net else 0 end) as chargeback_reversals,
    sum(case when type = 'adjustment' and lower(description) not like 'chargeback withdrawal%' and lower(description) not like 'chargeback reversal%' then net else 0 end) as other_adjustments,
    sum(case when type not in ('payout', 'transfer', 'charge', 'payment', 'refund', 'payment_refund', 'adjustment') then net else 0 end) as other_transactions
  from balance_transactions
  group by 1, 2
)

-- Compute the current_balance for each day and format output
select
  day,
  currency,
  -- use SUM Window Function to calc. running total
  sum(daily_balance) over(partition by currency order by day)/100.0 as current_balance,
  payouts/100.0 as payouts,
  net_transactions/100.0 as net_transactions,
  payments/100.0 as payments,
  refunds/100.0 as refunds,
  transfers/100.0 as transfers,
  chargeback_withdrawals/100.0 as chargeback_withdrawals,
  chargeback_reversals/100.0 as chargeback_reversals,
  other_adjustments/100.0 as other_adjustments,
  other_transactions/100.0 as other_transactions
from daily_balance_transactions
order by 1 desc, 2
          
        

  


  

        
        
        
        -- This template returns itemized subscription information for each customer and plan
select
  subscriptions.customer_id as customer_id,
  customers.email as customers_email,
  subscriptions.plan_id as plan_id,
  plans.nickname as plan_name,
  subscriptions.quantity as quantity,
  case when subscriptions.canceled_at is null and subscriptions.ended_at is null then 'active' else 'inactive' end as state,
  date_format(subscriptions.created, '%Y-%m-%d') as created_date,
  date_format(subscriptions.start, '%Y-%m-%d') as start_date,
  date_format(least(subscriptions.canceled_at, subscriptions.ended_at), '%Y-%m-%d') as end_date
from subscriptions
join plans -- join subscriptions on plans to get plan name
  on subscriptions.plan_id = plans.id
left join customers
  on subscriptions.customer_id = customers.id
order by 2
          
        

  


  

        
        
        
        -- This template returns the number of disputes grouped by reason and dispute status
select
  reason, -- reason given by cardholder for dispute
  status, -- current status of dispute
  count(id) as disputes,
  count_if(evidence_details_submission_count > 0) as disputes_with_evidence_submitted
from disputes
group by 1, 2
order by 3 desc
          
        

  


  

        
        
        
        -- This template returns itemized subscription information for each customer and plan
select
  subscriptions.customer_id as customer_id,
  customers.email as customers_email,
  subscriptions.plan_id as plan_id,
  plans.nickname as plan_name,
  subscriptions.quantity as quantity,
  case when subscriptions.canceled_at is null and subscriptions.ended_at is null then 'active' else 'inactive' end as state,
  date_format(subscriptions.created, '%Y-%m-%d') as created_date,
  date_format(subscriptions.start, '%Y-%m-%d') as start_date,
  date_format(least(subscriptions.canceled_at, subscriptions.ended_at), '%Y-%m-%d') as end_date
from subscriptions
join plans -- join subscriptions on plans to get plan name
  on subscriptions.plan_id = plans.id
left join customers
  on subscriptions.customer_id = customers.id
order by 2
          
        

  


  

        
        
        
        -- This template returns the number of charges and amounts for each card type, by currency

select
  coalesce(card_brand, 'Non-card or Other') as card_brand,
  currency,
  count(id) as charge_count,
  sum(amount)/100.0 as total_amount
from charges
where captured
group by 1, 2
order by 4 desc
          
        

  


  

        
        
        
        -- This template returns itemized information for charges and associated customer metadata (from charges_metadata)

-- charges_metadata_dictionary is a temporary table that creates a row for every charge_id with a dictionary of associated metadata
with charges_metadata_dictionary as (
  select
    charge_id,
    map_agg(key, value) metadata_dictionary -- MAP_AGG creates a key:value dictionary
  from charges_metadata
  group by 1)

select
  charges.created,
  charges.id,
  charges.customer_id,
  charges.amount/100.0 as amount,
  metadata_dictionary['customer_source'] as customer_source -- 'customer_source' is the key we are accessing and returning the associated value (provided via metadata)
from charges
left join charges_metadata_dictionary
  on charges.id = charges_metadata_dictionary.charge_id
where date_trunc('year', charges.created) = date_trunc('year', current_date)
  and charges.captured
order by charges.created desc
limit 1000