TIME-BASED SQL QUERIES

 

⏱️ TIME-BASED SQL QUERIES (CODING + LOGIC)

---

1️⃣ Find Users with N Transactions in X Minutes

🔥 MOST ASKED QUESTION

Table: transactions(user_id, txn_id, amount, txn_time)

✅ Requirement

Find users with ≥ 3 transactions in 10 minutes

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;

🗣 Explain

“This uses a rolling time window using a self-join. Very common in fraud detection.”

---

2️⃣ Consecutive Days / Events

🔥 Login, sales, activity streak questions

Table: logins(user_id, login_date)

✅ Find users with 3 consecutive login days

WITH cte AS (
  SELECT user_id,
         login_date,
         DATEADD(DAY,
           -ROW_NUMBER() OVER (
              PARTITION BY user_id ORDER BY login_date
           ),
           login_date
         ) grp
  FROM logins
)
SELECT user_id
FROM cte
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

🗣 Explain

“Subtracting row_number normalizes consecutive dates into the same group.”

---

3️⃣ Session Duration (Login → Logout)

Table: events(user_id, event_time)

SELECT user_id,
       event_time AS login_time,
       LEAD(event_time) OVER (
         PARTITION BY user_id ORDER BY event_time
       ) AS logout_time,
       DATEDIFF(
         MINUTE,
         event_time,
         LEAD(event_time) OVER (
           PARTITION BY user_id ORDER BY event_time
         )
       ) AS session_minutes
FROM events;

🗣 Senior touch

“This helps compute engagement metrics in analytics pipelines.”

---

4️⃣ Running Total by Date

Table: sales(order_date, amount)

SELECT order_date,
       SUM(amount) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales;

---

5️⃣ Moving Average (Last 7 Days)

SELECT order_date,
       AVG(amount) OVER (
         ORDER BY order_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7_days
FROM sales;

🗣 Explain

“This is a row-based window; for missing dates we’d switch to range-based logic.”

---

6️⃣ First and Last Transaction per Day

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY user_id, CAST(txn_time AS DATE)
           ORDER BY txn_time
         ) rn_asc,
         ROW_NUMBER() OVER (
           PARTITION BY user_id, CAST(txn_time AS DATE)
           ORDER BY txn_time DESC
         ) rn_desc
  FROM transactions
) t
WHERE rn_asc = 1 OR rn_desc = 1;

---

7️⃣ Time Gap Between Events

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

🗣 Explain

“Used to identify abnormal gaps or rapid activity.”

---

8️⃣ Peak Hour Analysis

Find hour with max transactions

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

---

9️⃣ Identify Overlapping Events

Table: meetings(start_time, end_time)

SELECT m1.*
FROM meetings m1
JOIN meetings m2
  ON m1.id <> m2.id
 AND m1.start_time < m2.end_time
 AND m1.end_time > m2.start_time;

---

🔑 TIME-BASED INTERVIEW PATTERNS (MEMORIZE)

Pattern	Function
Rolling window	BETWEEN time AND DATEADD()
Previous/Next	LAG / LEAD
Streaks	ROW_NUMBER + DATE math
Session	LEAD + DATEDIFF
Running total	SUM() OVER
Moving avg	ROWS BETWEEN

---

❌ COMMON MISTAKES (AVOID TOMORROW)

• Using WHERE instead of HAVING

• Forgetting PARTITION BY

• Ignoring duplicate timestamps

• Using correlated subqueries

• Not explaining window logic

---

🎯 HOW TO TALK LIKE A SENIOR ENGINEER

Say phrases like:

• “This logic is idempotent for reruns”

• “This scales well in Synapse / SQL DW”

• “This can be parameterized in ADF”

• “This avoids correlated subqueries”