অ্যানালিটিক্স প্যাটার্ন: গ্রোথ, কোহর্ট, রিটেনশন আর গ্যাপ

-- 1. সর্বোচ্চের চেয়ে ছোটদের মধ্যে সবচেয়ে বড়টা
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- 2. আলাদা আলাদা মান, একটা বাদ দিয়ে
SELECT DISTINCT salary AS second_highest
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- 3. DENSE_RANK, যা "N-তম"-তেও খাটে
WITH ranked AS (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT DISTINCT salary AS second_highest FROM ranked WHERE rnk = 2;

+----------------+
| second_highest |
+----------------+
|         180000 |
+----------------+
+----------------+
| second_highest |
+----------------+
|         180000 |
+----------------+
+----------------+
| second_highest |
+----------------+
|         180000 |
+----------------+

WITH ranked AS (
    SELECT department, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
),
departments AS (SELECT DISTINCT department FROM employees)
SELECT d.department,
       (SELECT MAX(r.salary) FROM ranked AS r
        WHERE r.department = d.department AND r.rnk = 2) AS second_highest,
       (SELECT COUNT(*) FROM ranked AS r
        WHERE r.department = d.department AND r.rnk = 2) AS people_at_it
FROM departments AS d
ORDER BY d.department;

+-------------+----------------+--------------+
| department  | second_highest | people_at_it |
+-------------+----------------+--------------+
| Data        |         110000 |            1 |
| Engineering |         120000 |            2 |
| Management  | NULL           |            0 |
+-------------+----------------+--------------+

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY month
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly
ORDER BY month;

+---------+---------+-------+------------+
| month   | revenue | prev  | growth_pct |
+---------+---------+-------+------------+
| 2026-01 |    6600 | NULL  | NULL       |
| 2026-02 |    7000 |  6600 |        6.1 |
| 2026-03 |   21400 |  7000 |      205.7 |
| 2026-04 |   23450 | 21400 |        9.6 |
| 2026-05 |    8500 | 23450 |      -63.8 |
| 2026-06 |   18100 |  8500 |      112.9 |
+---------+---------+-------+------------+

WITH RECURSIVE months(month) AS (
    SELECT '2026-01'
    UNION ALL
    SELECT strftime('%Y-%m', month || '-01', '+1 month') FROM months WHERE month < '2026-06'
),
sales AS (
    SELECT strftime('%Y-%m', o.order_date) AS month, SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    JOIN products AS p ON p.product_id = oi.product_id
    WHERE o.status <> 'cancelled' AND p.category_id = 3          -- Courses ক্যাটাগরি
    GROUP BY month
),
grid AS (
    SELECT m.month, COALESCE(s.revenue, 0) AS revenue
    FROM months AS m LEFT JOIN sales AS s ON s.month = m.month
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM grid
ORDER BY month;

+---------+---------+-------+------------+
| month   | revenue | prev  | growth_pct |
+---------+---------+-------+------------+
| 2026-01 |       0 | NULL  | NULL       |
| 2026-02 |    3000 |     0 | NULL       |
| 2026-03 |   15000 |  3000 |      400.0 |
| 2026-04 |       0 | 15000 |     -100.0 |
| 2026-05 |    3000 |     0 | NULL       |
| 2026-06 |   15000 |  3000 |      400.0 |
+---------+---------+-------+------------+

-- PostgreSQL: date_trunc তারিখকে মাসে ভাগ করে; generate_series ক্যালেন্ডার বানায়
WITH months AS (
    SELECT generate_series(DATE '2026-01-01', DATE '2026-06-01', INTERVAL '1 month')::date AS month
),
sales AS (
    SELECT date_trunc('month', o.order_date)::date AS month, SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    JOIN products AS p ON p.product_id = oi.product_id
    WHERE o.status <> 'cancelled' AND p.category_id = 3
    GROUP BY 1
)
SELECT to_char(m.month, 'YYYY-MM') AS month,
       COALESCE(s.revenue, 0) AS revenue,
       ROUND(100.0 * (COALESCE(s.revenue, 0) - LAG(COALESCE(s.revenue, 0)) OVER (ORDER BY m.month))
             / NULLIF(LAG(COALESCE(s.revenue, 0)) OVER (ORDER BY m.month), 0), 1) AS growth_pct
FROM months AS m
LEFT JOIN sales AS s ON s.month = m.month
ORDER BY m.month;

+---------+---------+------------+
| month   | revenue | growth_pct |
+---------+---------+------------+
| 2026-01 |       0 | NULL       |
| 2026-02 |    3000 | NULL       |
| 2026-03 |   15000 |      400.0 |
| 2026-04 |       0 |     -100.0 |
| 2026-05 |    3000 | NULL       |
| 2026-06 |   15000 |      400.0 |
+---------+---------+------------+

import random
from datetime import date, timedelta
from sqlhelp import con, show

random.seed(7)
con.execute("DROP TABLE IF EXISTS activity")
con.execute("""CREATE TABLE activity (
                   user_id   INTEGER NOT NULL,
                   active_on DATE    NOT NULL,
                   PRIMARY KEY (user_id, active_on))""")
rows = []
for user in range(1, 31):
    signup = date(2026, 1, 1) + timedelta(days=random.randrange(90))
    stays_for = random.choice([5, 20, 40, 70, 150])          # কত দিন পর আসা বন্ধ করবে
    day = signup
    while day < date(2026, 5, 1) and (day - signup).days < stays_for:
        rows.append((user, day.isoformat()))
        day += timedelta(days=random.choice([1, 1, 1, 2, 4, 7]))
con.executemany("INSERT INTO activity VALUES (?, ?)", rows)
con.commit()

show("SELECT COUNT(*) AS rows, COUNT(DISTINCT user_id) AS users, "
     "MIN(active_on) AS first_day, MAX(active_on) AS last_day FROM activity")

+------+-------+------------+------------+
| rows | users | first_day  | last_day   |
+------+-------+------------+------------+
|  525 |    30 | 2026-01-03 | 2026-04-30 |
+------+-------+------------+------------+

WITH user_months AS (
    SELECT DISTINCT user_id,
           CAST(strftime('%Y', active_on) AS INTEGER) * 12
         + CAST(strftime('%m', active_on) AS INTEGER) AS month_idx
    FROM activity
),
cohorts AS (
    SELECT user_id, MIN(month_idx) AS cohort_idx FROM user_months GROUP BY user_id
)
SELECT c.cohort_idx,
       um.month_idx - c.cohort_idx AS month_no,
       COUNT(*) AS active_users
FROM user_months AS um
JOIN cohorts AS c ON c.user_id = um.user_id
GROUP BY c.cohort_idx, month_no
ORDER BY c.cohort_idx, month_no;

+------------+----------+--------------+
| cohort_idx | month_no | active_users |
+------------+----------+--------------+
|      24313 |        0 |           12 |
|      24313 |        1 |           10 |
|      24313 |        2 |            7 |
|      24313 |        3 |            5 |
|      24314 |        0 |            9 |
|      24314 |        1 |            5 |
|      24314 |        2 |            3 |
|      24315 |        0 |            9 |
|      24315 |        1 |            7 |
+------------+----------+--------------+

WITH user_months AS (
    SELECT DISTINCT user_id,
           CAST(strftime('%Y', active_on) AS INTEGER) * 12
         + CAST(strftime('%m', active_on) AS INTEGER) AS month_idx
    FROM activity
),
cohorts AS (
    SELECT user_id, MIN(month_idx) AS cohort_idx FROM user_months GROUP BY user_id
),
counts AS (
    SELECT c.cohort_idx, um.month_idx - c.cohort_idx AS month_no, COUNT(*) AS users
    FROM user_months AS um JOIN cohorts AS c ON c.user_id = um.user_id
    GROUP BY c.cohort_idx, month_no
),
last AS (SELECT MAX(month_idx) AS last_idx FROM user_months)
SELECT printf('%d-%02d', (cohort_idx - 1) / 12, (cohort_idx - 1) % 12 + 1) AS cohort,
       MAX(CASE WHEN month_no = 0 THEN users END) AS size,
       ROUND(100.0 * MAX(CASE WHEN month_no = 1 THEN users END)
             / MAX(CASE WHEN month_no = 0 THEN users END), 0) AS m1_pct,
       CASE WHEN cohort_idx + 2 <= last_idx THEN
            ROUND(100.0 * COALESCE(MAX(CASE WHEN month_no = 2 THEN users END), 0)
                  / MAX(CASE WHEN month_no = 0 THEN users END), 0) END AS m2_pct,
       CASE WHEN cohort_idx + 3 <= last_idx THEN
            ROUND(100.0 * COALESCE(MAX(CASE WHEN month_no = 3 THEN users END), 0)
                  / MAX(CASE WHEN month_no = 0 THEN users END), 0) END AS m3_pct
FROM counts CROSS JOIN last
GROUP BY cohort_idx, last_idx
ORDER BY cohort_idx;

+---------+------+--------+--------+--------+
| cohort  | size | m1_pct | m2_pct | m3_pct |
+---------+------+--------+--------+--------+
| 2026-01 |   12 |   83.0 |   58.0 |   42.0 |
| 2026-02 |    9 |   56.0 |   33.0 | NULL   |
| 2026-03 |    9 |   78.0 | NULL   | NULL   |
+---------+------+--------+--------+--------+

SELECT active_on,
       ROW_NUMBER() OVER (ORDER BY active_on) AS rn,
       date(active_on, '-' || ROW_NUMBER() OVER (ORDER BY active_on) || ' days') AS island
FROM activity
WHERE user_id = 6
ORDER BY active_on;

+------------+----+------------+
| active_on  | rn | island     |
+------------+----+------------+
| 2026-02-18 |  1 | 2026-02-17 |
| 2026-02-19 |  2 | 2026-02-17 |
| 2026-02-20 |  3 | 2026-02-17 |
| 2026-02-21 |  4 | 2026-02-17 |
| 2026-02-22 |  5 | 2026-02-17 |
| 2026-02-23 |  6 | 2026-02-17 |
| 2026-03-02 |  7 | 2026-02-23 |
| 2026-03-03 |  8 | 2026-02-23 |
| 2026-03-04 |  9 | 2026-02-23 |
| 2026-03-06 | 10 | 2026-02-24 |
+------------+----+------------+

WITH labelled AS (
    SELECT user_id, active_on,
           julianday(active_on)
             - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_on) AS island
    FROM activity
),
streaks AS (
    SELECT user_id, MIN(active_on) AS started, MAX(active_on) AS ended, COUNT(*) AS days
    FROM labelled
    GROUP BY user_id, island
)
SELECT user_id, started, ended, days
FROM streaks
WHERE days >= 6
ORDER BY days DESC, user_id;

+---------+------------+------------+------+
| user_id | started    | ended      | days |
+---------+------------+------------+------+
|      29 | 2026-03-14 | 2026-03-22 |    9 |
|      30 | 2026-02-18 | 2026-02-26 |    9 |
|      20 | 2026-01-18 | 2026-01-24 |    7 |
|      21 | 2026-02-27 | 2026-03-05 |    7 |
|       6 | 2026-02-18 | 2026-02-23 |    6 |
|      26 | 2026-04-25 | 2026-04-30 |    6 |
+---------+------------+------------+------+

CREATE TABLE chat_events (
    user_id INTEGER NOT NULL,
    sent_at TEXT    NOT NULL
);
INSERT INTO chat_events (user_id, sent_at) VALUES
    (1, '2026-05-04 09:00'), (1, '2026-05-04 09:05'), (1, '2026-05-04 09:20'),
    (1, '2026-05-04 10:30'), (1, '2026-05-04 10:31'), (1, '2026-05-04 14:00'),
    (2, '2026-05-04 09:10'), (2, '2026-05-04 09:50'), (2, '2026-05-04 09:55');

WITH gaps AS (
    SELECT user_id, sent_at,
           ROUND((julianday(sent_at)
                  - julianday(LAG(sent_at) OVER (PARTITION BY user_id ORDER BY sent_at))) * 24 * 60)
               AS gap_min
    FROM chat_events
),
flagged AS (
    SELECT *, CASE WHEN gap_min IS NULL OR gap_min > 30 THEN 1 ELSE 0 END AS new_session
    FROM gaps
)
SELECT user_id, sent_at, gap_min, new_session,
       SUM(new_session) OVER (PARTITION BY user_id ORDER BY sent_at
                              ROWS UNBOUNDED PRECEDING) AS session_no
FROM flagged
ORDER BY user_id, sent_at;

+---------+------------------+---------+-------------+------------+
| user_id | sent_at          | gap_min | new_session | session_no |
+---------+------------------+---------+-------------+------------+
|       1 | 2026-05-04 09:00 | NULL    |           1 |          1 |
|       1 | 2026-05-04 09:05 |     5.0 |           0 |          1 |
|       1 | 2026-05-04 09:20 |    15.0 |           0 |          1 |
|       1 | 2026-05-04 10:30 |    70.0 |           1 |          2 |
|       1 | 2026-05-04 10:31 |     1.0 |           0 |          2 |
|       1 | 2026-05-04 14:00 |   209.0 |           1 |          3 |
|       2 | 2026-05-04 09:10 | NULL    |           1 |          1 |
|       2 | 2026-05-04 09:50 |    40.0 |           1 |          2 |
|       2 | 2026-05-04 09:55 |     5.0 |           0 |          2 |
+---------+------------------+---------+-------------+------------+

-- সহজ
WITH ranked AS (
    SELECT price, DENSE_RANK() OVER (ORDER BY price DESC) AS rnk FROM products
)
SELECT DISTINCT price FROM ranked WHERE rnk = 3;

-- মাঝারি
WITH monthly AS (
    SELECT strftime('%Y-%m', active_on) AS month, COUNT(DISTINCT user_id) AS users
    FROM activity
    GROUP BY month
)
SELECT month, users,
       ROUND(100.0 * (users - LAG(users) OVER (ORDER BY month))
             / NULLIF(LAG(users) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly
ORDER BY month;

-- কঠিন
WITH labelled AS (
    SELECT user_id,
           julianday(active_on) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_on) AS island
    FROM activity
),
streaks AS (
    SELECT user_id, COUNT(*) AS days FROM labelled GROUP BY user_id, island
)
SELECT user_id, MAX(days) AS longest_streak
FROM streaks
GROUP BY user_id
ORDER BY longest_streak DESC, user_id
LIMIT 5;