Posts

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 ...
Post a Comment
Read more

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 FR...
Post a Comment
Read more

ALL IMPORTANT PATTERNS for Running Total and Moving Average

-
  These two topics are VERY HIGH-FREQUENCY interview questions . Below are ALL IMPORTANT PATTERNS for Running Total and Moving Average (Last 3 records) — with multiple SQL styles , edge cases , and interview tips . šŸ”¹ 1. RUNNING TOTAL (CUMULATIVE SUM) šŸ“Œ Sample Table sales ----------------------- sale_date | amount 2024 - 01 - 01 | 100 2024 - 01 - 02 | 200 2024 - 01 - 03 | 150 2024 - 01 - 04 | 300 ✅ Pattern 1: Window Function (BEST & EXPECTED) SELECT sale_date, amount, SUM(amount) OVER ( ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM sales; šŸ”¹ Output sale_date amount running_total 01-Jan 100 100 02-Jan 200 300 03-Jan 150 450 04-Jan 300 750 ✔ Most optimal ✔ Works in SQL Server / Databricks / Oracle / Postgres ✅ Pattern 2: Running Total Per Group (Partition) SELECT region, sale_date, amount, SUM(amount) OVER ( PARTITION BY region ORDER ...
Post a Comment
Read more

SCD TYPE 2 – INTERVIEW QUESTIONS + MERGE CODE

-
 Below is a COMPLETE, interview-ready guide to SCD Type 2 using MERGE , covering ALL POSSIBLE QUESTIONS (theory + edge cases) AND production-grade SQL code . This is curated for senior data engineer / Azure / Databricks / SQL Server interviews. šŸ”¹ SCD TYPE 2 – INTERVIEW QUESTIONS + MERGE CODE 1️⃣ What is SCD Type 2? Answer: SCD Type 2 maintains full historical changes by: Expiring old records Inserting a new row for every change Typical columns: effective_start_date effective_end_date is_current version (optional) 2️⃣ SCD Type 2 Table Design (Interview MUST) šŸŽÆ Dimension Table CREATE TABLE dim_customer ( customer_sk INT IDENTITY ( 1 , 1 ), customer_id INT , name VARCHAR ( 100 ), city VARCHAR ( 100 ), effective_start_date DATE , effective_end_date DATE , is_current CHAR ( 1 ) ); šŸŽÆ Staging Table CREATE TABLE stg_customer ( customer_id INT , name VARCHAR ( 100 ), city VARCHAR ( 100 ), load_date ...
Post a Comment
Read more