-

 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

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

PySpark Interview Questions with Detailed Answers

-
  Spark Internals & Execution 1. Explain the full lifecycle of a Spark job — from code to DAG to task execution. Definition: The Spark job lifecycle begins when the driver program initiates an action (e.g., .collect() , .count() ). Here's a step-by-step breakdown: User Code: You write transformations and actions in PySpark. Logical Plan: Spark creates a logical plan based on your transformations. Optimization: Catalyst optimizer optimizes the logical plan. Physical Plan: Spark creates a physical plan with stages. DAG (Directed Acyclic Graph): Stages are translated into a DAG of tasks. Task Scheduling: Tasks are distributed across executors. Execution: Executors run the tasks in parallel. Healthcare Example: In processing electronic health records (EHR), you may filter patients by diagnosis, join with lab results, and then aggregate. Spark translates this pipeline into optimized stages and tasks. 2. What is the difference between transformations and actions? Why are tra...
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 ...
Read more