SQL Formatter & Beautifier

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

-- Monthly Active Subscribers & Revenue Performance
WITH monthly_metrics AS(
  SELECT u. id AS user_id,
  u. full_name,
  u. email,
  s. plan_name,
  s. price,
  DATE_TRUNC('month', p. payment_date) AS payment_month,
  ROW_NUMBER() OVER(PARTITION BY u. id
  ORDER BY p. payment_date DESC) AS payment_rank
  FROM users u
  INNER JOIN subscriptions s
  ON u. subscription_id = s. id
  LEFT JOIN payments p
  ON p. user_id = u. id
  AND p. status = 'COMPLETED'
  WHERE u. is_active = TRUE
  AND u. created_at >= '2025-01-01'
  AND s. price > 0)
SELECT plan_name,
payment_month,
COUNT(DISTINCT user_id) AS total_subscribers,
SUM(price) AS gross_revenue,
CASE
WHEN SUM(price) >= 10000 THEN 'Tier 1 Enterprise'
WHEN SUM(price) >= 2500 THEN 'Tier 2 Mid-Market'
ELSE 'Standard Growth' END AS revenue_tier
FROM monthly_metrics
WHERE payment_rank = 1
GROUP BY plan_name,
payment_month
HAVING COUNT(DISTINCT user_id) > 5
ORDER BY gross_revenue DESC,
total_subscribers DESC
LIMIT 50;