Latihan SQL untuk analisis cohort dan retention

CREATE TABLE orders (
  id          BIGINT         PRIMARY KEY,
  user_id     INT            NOT NULL,
  order_date  DATE           NOT NULL,
  amount      DECIMAL(10,2) NOT NULL
);

-- Contoh data (1000 user, 3 tahun transaksi)
-- user_id 1: cohort Jan 2023, aktif 6 bulan
-- user_id 2: cohort Jan 2023, hanya beli sekali
-- user_id 3: cohort Feb 2023, aktif 12 bulan
-- dst...

-- Di SQL Lab BASEGROUND, jalankan:
-- SELECT * FROM orders LIMIT 10;

WITH cohort AS (
  SELECT
    user_id,
    DATE_FORMAT(MIN(order_date), '%Y-%m-01')  AS cohort_month
    -- PostgreSQL: DATE_TRUNC('month', MIN(order_date))::DATE
  FROM  orders
  GROUP BY user_id
)
SELECT
  cohort_month,
  COUNT(*) AS cohort_size
FROM  cohort
GROUP BY cohort_month
ORDER BY cohort_month;

WITH
cohort AS (
  SELECT
    user_id,
    DATE_FORMAT(MIN(order_date), '%Y-%m-01') AS cohort_month
  FROM orders
  GROUP BY user_id
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_FORMAT(order_date, '%Y-%m-01')        AS active_month
  FROM orders
)
SELECT
  c.user_id,
  c.cohort_month,
  a.active_month,
  TIMESTAMPDIFF(MONTH, c.cohort_month, a.active_month) AS month_number
  -- PostgreSQL: EXTRACT(YEAR FROM AGE(a.active_month, c.cohort_month)) * 12
  --            + EXTRACT(MONTH FROM AGE(a.active_month, c.cohort_month))
FROM  cohort   c
JOIN  activity a ON c.user_id = a.user_id
ORDER BY c.user_id, month_number
LIMIT 10;

WITH
cohort AS (
  SELECT
    user_id,
    DATE_FORMAT(MIN(order_date), '%Y-%m-01') AS cohort_month
  FROM orders
  GROUP BY user_id
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_FORMAT(order_date, '%Y-%m-01') AS active_month
  FROM orders
),
cohort_activity AS (
  SELECT
    c.cohort_month,
    TIMESTAMPDIFF(MONTH, c.cohort_month, a.active_month) AS month_number,
    COUNT(DISTINCT c.user_id) AS active_users
  FROM  cohort   c
  JOIN  activity a ON c.user_id = a.user_id
  GROUP BY c.cohort_month, month_number
),
cohort_size AS (
  SELECT cohort_month, COUNT(*) AS total_users
  FROM  cohort
  GROUP BY cohort_month
)
SELECT
  ca.cohort_month,
  ca.month_number,
  ca.active_users,
  cs.total_users,
  ROUND(ca.active_users / cs.total_users * 100, 1) AS retention_pct
FROM  cohort_activity ca
JOIN  cohort_size     cs ON ca.cohort_month = cs.cohort_month
ORDER BY ca.cohort_month, ca.month_number;

WITH
retention_raw AS (
  -- ... salin query retention rate dari langkah 3 di sini ...
  -- atau gunakan sebagai subquery
)
SELECT
  cohort_month,
  MAX(CASE WHEN month_number = 0 THEN CONCAT(retention_pct, '%') END) AS `M0`,
  MAX(CASE WHEN month_number = 1 THEN CONCAT(retention_pct, '%') END) AS `M1`,
  MAX(CASE WHEN month_number = 2 THEN CONCAT(retention_pct, '%') END) AS `M2`,
  MAX(CASE WHEN month_number = 3 THEN CONCAT(retention_pct, '%') END) AS `M3`,
  MAX(CASE WHEN month_number = 4 THEN CONCAT(retention_pct, '%') END) AS `M4`,
  MAX(CASE WHEN month_number = 5 THEN CONCAT(retention_pct, '%') END) AS `M5`,
  MAX(CASE WHEN month_number = 6 THEN CONCAT(retention_pct, '%') END) AS `M6`
FROM  retention_raw
GROUP BY cohort_month
ORDER BY cohort_month;

-- Asumsi: tabel events (user_id, event_name, event_date)
SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'view_product' THEN user_id END)
    AS s1_lihat,

  COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart'  THEN user_id END)
    AS s2_keranjang,

  COUNT(DISTINCT CASE WHEN event_name = 'checkout'     THEN user_id END)
    AS s3_checkout,

  COUNT(DISTINCT CASE WHEN event_name = 'purchase'     THEN user_id END)
    AS s4_beli,

  ROUND(
    COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) * 100.0 /
    NULLIF(COUNT(DISTINCT CASE WHEN event_name = 'view_product' THEN user_id END), 0), 1
  ) AS lihat_ke_keranjang_pct,

  ROUND(
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) * 100.0 /
    NULLIF(COUNT(DISTINCT CASE WHEN event_name = 'view_product' THEN user_id END), 0), 1
  ) AS konversi_total_pct

FROM events
WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31';

WITH cohort AS (
  SELECT
    user_id,
    DATE_FORMAT(MIN(order_date), '%Y-%m-01') AS cohort_month
  FROM orders
  GROUP BY user_id
)
SELECT
  c.cohort_month,
  COUNT(DISTINCT c.user_id)                          AS cohort_size,
  COUNT(o.id)                                         AS total_orders,
  ROUND(SUM(o.amount), 2)                             AS total_revenue,
  ROUND(SUM(o.amount) / COUNT(DISTINCT c.user_id), 2) AS ltv_per_user,
  ROUND(AVG(o.amount), 2)                             AS avg_order_value,
  ROUND(COUNT(o.id) * 1.0 / COUNT(DISTINCT c.user_id), 1) AS avg_orders_per_user
FROM   cohort c
JOIN   orders o ON c.user_id = o.user_id
GROUP BY c.cohort_month
ORDER BY c.cohort_month;