TIME-SERIES SQL

 

🔥 TIME-SERIES SQL (MSSQL SERVER ONLY)

---

📌 Sample Table (Used in All Queries)

CREATE TABLE transactions (
    user_id INT,
    txn_time DATETIME,
    amount INT
);

---

🟢 SIMPLE LEVEL

---

1️⃣ Running Total (Cumulative Sum)

SELECT
    user_id,
    txn_time,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY txn_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM transactions;

---

2️⃣ Row-Based Moving Average (Last 3 Records)

SELECT
    user_id,
    txn_time,
    amount,
    AVG(amount) OVER (
        PARTITION BY user_id
        ORDER BY txn_time
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM transactions;

---

3️⃣ Time Difference Between Events

SELECT
    user_id,
    txn_time,
    LAG(txn_time) OVER (
        PARTITION BY user_id
        ORDER BY txn_time
    ) AS prev_time,
    DATEDIFF(
        MINUTE,
        LAG(txn_time) OVER (PARTITION BY user_id ORDER BY txn_time),
        txn_time
    ) AS diff_minutes
FROM transactions;

---

4️⃣ First & Last Event Per Day

SELECT
    user_id,
    CAST(txn_time AS DATE) AS txn_date,
    MIN(txn_time) AS first_txn,
    MAX(txn_time) AS last_txn
FROM transactions
GROUP BY user_id, CAST(txn_time AS DATE);

---

5️⃣ Daily Aggregation

SELECT
    CAST(txn_time AS DATE) AS txn_date,
    SUM(amount) AS total_amount
FROM transactions
GROUP BY CAST(txn_time AS DATE);

---

🟡 INTERMEDIATE LEVEL

---

6️⃣ Rolling Sum – Last 10 Minutes (Self-Join)

SELECT
    t1.user_id,
    t1.txn_time,
    SUM(t2.amount) AS sum_last_10_min
FROM transactions t1
JOIN transactions t2
  ON t1.user_id = t2.user_id
 AND t2.txn_time BETWEEN DATEADD(MINUTE, -10, t1.txn_time)
                      AND t1.txn_time
GROUP BY t1.user_id, t1.txn_time;

✔ MSSQL-safe time window
⚠ Slower on large tables

---

7️⃣ Count Events in Last 10 Minutes

SELECT
    t1.user_id,
    t1.txn_time,
    COUNT(*) AS txn_count
FROM transactions t1
JOIN transactions t2
  ON t1.user_id = t2.user_id
 AND t2.txn_time BETWEEN DATEADD(MINUTE, -10, t1.txn_time)
                      AND t1.txn_time
GROUP BY t1.user_id, t1.txn_time;

---

8️⃣ Detect Gaps Between Records (>30 min)

SELECT
    user_id,
    txn_time,
    CASE
        WHEN DATEDIFF(
            MINUTE,
            LAG(txn_time) OVER (PARTITION BY user_id ORDER BY txn_time),
            txn_time
        ) > 30
        THEN 1 ELSE 0
    END AS gap_flag
FROM transactions;

---

9️⃣ Sessionization (Session ID)

WITH cte AS (
    SELECT *,
           CASE
               WHEN DATEDIFF(
                   MINUTE,
                   LAG(txn_time) OVER (PARTITION BY user_id ORDER BY txn_time),
                   txn_time
               ) > 30
               THEN 1 ELSE 0
           END AS new_session
    FROM transactions
)
SELECT *,
       SUM(new_session) OVER (
           PARTITION BY user_id
           ORDER BY txn_time
       ) AS session_id
FROM cte;

---

🔴 ADVANCED LEVEL (INTERVIEW FAVORITES)

---

🔟 Users with ≥3 Events in Any 10-Minute Window

SELECT DISTINCT t1.user_id
FROM transactions t1
JOIN transactions t2
  ON t1.user_id = t2.user_id
 AND t2.txn_time BETWEEN t1.txn_time
                      AND DATEADD(MINUTE, 10, t1.txn_time)
GROUP BY t1.user_id, t1.txn_time
HAVING COUNT(*) >= 3;

---

1️⃣1️⃣ Rolling 7-Day Sum (Correlated Subquery)

SELECT
    t1.txn_time,
    (
        SELECT SUM(t2.amount)
        FROM transactions t2
        WHERE t2.txn_time BETWEEN DATEADD(DAY, -7, t1.txn_time)
                              AND t1.txn_time
    ) AS rolling_7_day_sum
FROM transactions t1;

---

1️⃣2️⃣ Peak Hour Analysis

SELECT
    DATEPART(HOUR, txn_time) AS hour_of_day,
    COUNT(*) AS txn_count
FROM transactions
GROUP BY DATEPART(HOUR, txn_time)
ORDER BY txn_count DESC;

---

1️⃣3️⃣ Find Missing Dates (Daily Series)

WITH dates AS (
    SELECT MIN(CAST(txn_time AS DATE)) AS dt
    FROM transactions
    UNION ALL
    SELECT DATEADD(DAY, 1, dt)
    FROM dates
    WHERE dt < (SELECT MAX(CAST(txn_time AS DATE)) FROM transactions)
)
SELECT dt AS missing_date
FROM dates
WHERE dt NOT IN (
    SELECT DISTINCT CAST(txn_time AS DATE)
    FROM transactions
)
OPTION (MAXRECURSION 1000);

---

1️⃣4️⃣ Last Known Value Forward-Fill

SELECT
    txn_time,
    MAX(amount) OVER (
        ORDER BY txn_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS last_known_value
FROM transactions;

---

🧠 MSSQL TIME-SERIES CHEAT SHEET

Requirement	MSSQL Technique
Time window	Self-join
Running total	SUM() OVER
Moving average	ROWS BETWEEN
Gap detection	LAG + DATEDIFF
Sessionization	CTE + window sum
Missing dates	Recursive CTE

---

🎯 INTERVIEW POWER LINE

“SQL Server lacks native time-based RANGE windows, so I use self-joins or correlated subqueries for rolling time windows.”