SQL Insights Stripe App is Live

SELECT
  to_timestamp(created) AS created_at,
  id,
  amount,
  type,
  currency,
  source_id
FROM stripe_balance_transactions
ORDER BY created desc
LIMIT 5;

SELECT
  to_timestamp(created) AS created_at,
  id,
  amount,
  type,
  currency,
  source_id
FROM stripe_balance_transactions
WHERE to_timestamp(created) > current_date - interval '1 month'
ORDER BY created desc;

SELECT
  to_timestamp(created) AS created_at,
  id,
  amount,
  type,
  currency,
  source_id
FROM stripe_balance_transactions
WHERE extract(quarter from to_timestamp(created)) = extract(quarter from current_date)
  AND extract(year from to_timestamp(created)) = extract(year from current_date)
ORDER BY created desc;

SELECT
  to_timestamp(created) AS created_at,
  id,
  amount,
  type,
  currency,
  source_id
FROM stripe_balance_transactions
WHERE type = 'payout'
  AND extract(quarter from to_timestamp(created)) = extract(quarter from current_date)
  AND extract(year from to_timestamp(created)) = extract(year from current_date)
ORDER BY created desc;

WITH charges_timezone_conversion AS (
  SELECT
    to_timestamp(created) AT time zone 'America/Los_Angeles' AS timestamp_pacific,
    currency,
    amount
  FROM stripe_charges
  WHERE captured -- filter out uncaptured charges
)

SELECT
  to_char(timestamp_pacific, 'YYYY-MM') as month,
  currency,
  round(sum(amount)/100.0, 2) AS gross_charges
FROM charges_timezone_conversion
WHERE timestamp_pacific >= current_timestamp AT time zone 'America/Los_Angeles' - interval '24 months'
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

-- if a customer has only one invoice, categorize them as a new customer
WITH customer_status AS (
  SELECT
    case when count(id) > 1 then false else true end AS new_customer,
    customer_id
  FROM stripe_invoices
  GROUP BY 2
)

SELECT
  concat(stripe_invoices.metadata->>'PeriodYear','-',lpad(stripe_invoices.metadata->>'PeriodMonth',2,'0')) AS invoice_month,
  customer_status.new_customer,
  round(sum(stripe_invoices.subtotal)/100.0, 2) AS invoiced_subtotal
FROM stripe_invoices
JOIN customer_status
  ON customer_status.customer_id = stripe_invoices.customer_id
GROUP BY 1, 2;

SELECT
  concat(stripe_invoices.metadata->>'PeriodYear','-',lpad(stripe_invoices.metadata->>'PeriodMonth',2,'0')) AS invoice_month,
  count(1) AS total_invoices,
  sum(case when attempt_count > 1 then 1 else 0 end) AS multiple_attempt_invoices,
  100.0*sum(case when attempt_count > 1 then 1 else 0 end)/count(1) AS percent_multiple_attempts
FROM stripe_invoices
GROUP BY 1;