Templates & Examples

SELECT
  date_trunc('day', created_at) AS day,
  sum(amount) AS gross_amount
FROM analytics.payments
WHERE status = 'succeeded'
  AND created_at >= :start AND created_at < :end
GROUP BY 1
ORDER BY 1;

SELECT
  payment_method_type,
  sum(amount) AS gross_amount
FROM analytics.payments
WHERE status = 'succeeded'
  AND created_at >= :start AND created_at < :end
GROUP BY 1
ORDER BY gross_amount DESC;

WITH charges AS (
  SELECT count(*) AS cnt
  FROM analytics.payments
  WHERE status = 'succeeded'
    AND created_at >= now() - interval '30 days'
),
refunds AS (
  SELECT count(*) AS cnt
  FROM analytics.refunds
  WHERE created_at >= now() - interval '30 days'
)
SELECT refunds.cnt::decimal / nullif(charges.cnt, 0) AS refund_rate;

SELECT
  p.id AS payout_id,
  p.arrival_date,
  sum(bt.net) AS net_total,
  sum(bt.fee) AS fee_total
FROM analytics.payouts p
JOIN analytics.balance_transactions bt
  ON bt.source_id = p.id
WHERE p.arrival_date >= :start AND p.arrival_date < :end
GROUP BY 1, 2
ORDER BY 2 DESC;

SELECT
  c.id,
  coalesce(c.email, '(no email)') AS email,
  sum(p.amount) AS lifetime_value
FROM analytics.customers c
JOIN analytics.payments p
  ON p.customer_id = c.id
WHERE p.status = 'succeeded'
GROUP BY 1, 2
ORDER BY lifetime_value DESC
LIMIT 50;