-

 VERY HARD – Pattern 1 (GROUP BY + HAVING)

Level: Senior Data Engineer / 5–8+ years

🧩 DATASET 1: BANKING (Fraud + Behavioral Analysis)

Table: transactions

txn_id | account_id | txn_date   | amount | txn_type
-----------------------------------------------------
1      | 101        | 2025-01-01 | 5000   | CREDIT
2      | 101        | 2025-01-01 | 2000   | DEBIT
3      | 101        | 2025-01-02 | 10000  | DEBIT
4      | 102        | 2025-01-01 | 8000   | CREDIT
5      | 102        | 2025-01-02 | 9000   | DEBIT

🔥 Q1. Find accounts where total debit > total credit

✅ SQL Advanced

SELECT account_id,
       SUM(CASE WHEN txn_type='DEBIT' THEN amount ELSE 0 END) AS total_debit,
       SUM(CASE WHEN txn_type='CREDIT' THEN amount ELSE 0 END) AS total_credit
FROM transactions
GROUP BY account_id
HAVING SUM(CASE WHEN txn_type='DEBIT' THEN amount ELSE 0 END) >
       SUM(CASE WHEN txn_type='CREDIT' THEN amount ELSE 0 END);

✅ PySpark Advanced

from pyspark.sql.functions import sum, when, col

df.groupBy("account_id").agg(
    sum(when(col("txn_type")=="DEBIT", col("amount")).otherwise(0)).alias("total_debit"),
    sum(when(col("txn_type")=="CREDIT", col("amount")).otherwise(0)).alias("total_credit")
).filter(col("total_debit") > col("total_credit")).show()

🔥 Q2. Find accounts where average debit amount > 2× average credit amount

SQL

SELECT account_id
FROM transactions
GROUP BY account_id
HAVING AVG(CASE WHEN txn_type='DEBIT' THEN amount END) >
       2 * AVG(CASE WHEN txn_type='CREDIT' THEN amount END);

PySpark

from pyspark.sql.functions import avg

df.groupBy("account_id").agg(
    avg(when(col("txn_type")=="DEBIT", col("amount"))).alias("avg_debit"),
    avg(when(col("txn_type")=="CREDIT", col("amount"))).alias("avg_credit")
).filter(col("avg_debit") > 2*col("avg_credit")).show()

🧩 DATASET 2: HR Analytics

Table: employees

emp_id | dept_id | salary | gender
-----------------------------------
1      | 10      | 50000  | M
2      | 10      | 60000  | F
3      | 10      | 70000  | F
4      | 20      | 30000  | M
5      | 20      | 35000  | M

🔥 Q3. Find departments where female average salary > male average salary

SQL

SELECT dept_id
FROM employees
GROUP BY dept_id
HAVING AVG(CASE WHEN gender='F' THEN salary END) >
       AVG(CASE WHEN gender='M' THEN salary END);

PySpark

df.groupBy("dept_id").agg(
    avg(when(col("gender")=="F", col("salary"))).alias("avg_female"),
    avg(when(col("gender")=="M", col("salary"))).alias("avg_male")
).filter(col("avg_female") > col("avg_male")).show()

🔥 Q4. Find departments where salary variance > 10,000

SQL

SELECT dept_id,
       MAX(salary) - MIN(salary) AS salary_gap
FROM employees
GROUP BY dept_id
HAVING MAX(salary) - MIN(salary) > 10000;

PySpark

from pyspark.sql.functions import max, min

df.groupBy("dept_id").agg(
    (max("salary") - min("salary")).alias("salary_gap")
).filter("salary_gap > 10000").show()

🧩 DATASET 3: E-COMMERCE (Behavioral Segmentation)

Table: orders

order_id | customer_id | product_id | amount
------------------------------------------------
1        | 100         | A          | 200
2        | 100         | B          | 300
3        | 100         | A          | 100
4        | 101         | C          | 500

🔥 Q5. Find customers purchasing more than 2 distinct products

SQL

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) > 2;

PySpark

from pyspark.sql.functions import countDistinct

df.groupBy("customer_id") \
  .agg(countDistinct("product_id").alias("prod_count")) \
  .filter("prod_count > 2") \
  .show()

🔥 Q6. Find customers whose max order > 50% of their total spend

SQL

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING MAX(amount) > 0.5 * SUM(amount);

PySpark

from pyspark.sql.functions import max, sum

df.groupBy("customer_id").agg(
    max("amount").alias("max_amt"),
    sum("amount").alias("total_amt")
).filter(col("max_amt") > 0.5*col("total_amt")).show()

🧩 DATASET 4: App / Platform Logs

Table: user_sessions

user_id | session_id | duration_minutes | device
-------------------------------------------------
1       | S1         | 30               | Android
1       | S2         | 5                | Android
2       | S3         | 100              | iOS
2       | S4         | 120              | iOS

🔥 Q7. Find users whose total session duration > global average duration

SQL

SELECT user_id
FROM user_sessions
GROUP BY user_id
HAVING SUM(duration_minutes) >
      (SELECT AVG(duration_minutes) FROM user_sessions);

PySpark

global_avg = df.agg(avg("duration_minutes")).collect()[0][0]

df.groupBy("user_id") \
  .agg(sum("duration_minutes").alias("total_duration")) \
  .filter(col("total_duration") > global_avg) \
  .show()

🔥 Q8. Find devices where average session duration is 2× overall average

SQL

SELECT device
FROM user_sessions
GROUP BY device
HAVING AVG(duration_minutes) >
       2 * (SELECT AVG(duration_minutes) FROM user_sessions);

🧩 DATASET 5: Payments (Data Quality Check)

Table: payments

payment_id | merchant_id | status   | amount
---------------------------------------------
1          | M1          | SUCCESS  | 100
2          | M1          | FAILED   | 200
3          | M2          | SUCCESS  | 300

🔥 Q9. Find merchants where failed transactions > 30% of total

SQL

SELECT merchant_id
FROM payments
GROUP BY merchant_id
HAVING 
    SUM(CASE WHEN status='FAILED' THEN 1 ELSE 0 END) * 1.0
    / COUNT(*) > 0.3;

PySpark

df.groupBy("merchant_id").agg(
    (sum(when(col("status")=="FAILED",1).otherwise(0))
     / count("*")).alias("failure_ratio")
).filter("failure_ratio > 0.3").show()

🔥 Q10. Find merchants where SUCCESS amount < 50% of total processed amount

SQL

SELECT merchant_id
FROM payments
GROUP BY merchant_id
HAVING 
    SUM(CASE WHEN status='SUCCESS' THEN amount ELSE 0 END)
    < 0.5 * SUM(amount);

PySpark

df.groupBy("merchant_id").agg(
    sum(when(col("status")=="SUCCESS", col("amount")).otherwise(0)).alias("success_amt"),
    sum("amount").alias("total_amt")
).filter(col("success_amt") < 0.5*col("total_amt")).show()

Comments

Post a Comment

Popular posts from this blog

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 ...
Read more

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 ...
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...
Read more