Tricky SQL Queries for interview Preparation

Sample Tables for Solving Queries

1. Products Table

product_id	product_name	category_id	category_name
101	Smartphone	1	Electronics
102	Laptop	1	Electronics
103	Blender	2	Kitchen
104	Coffee Maker	2	Kitchen
105	Hair Dryer	3	Beauty
106	Perfume	3	Beauty

2. Sales Table

sale_id	customer_id	product_id	salesperson_id	sale_date	quantity	total_amount
1	501	101	1001	2024-01-15	2	800
2	502	102	1002	2024-01-20	1	1500
3	501	103	1003	2024-02-05	3	300
4	503	104	1001	2024-02-25	1	100
5	504	105	1004	2024-03-15	4	500

3. Salespersons Table

salesperson_id	salesperson_name	region
1001	John Doe	North
1002	Jane Smith	South
1003	Alex Johnson	East
1004	Emily Davis	West

Explanation of Sample Data

• The Products table contains information about various products, including their categories.
• The Sales table captures individual sales transactions, including customer, product, and salesperson details, along with sale dates, quantities, and total amounts.
• The Salespersons table provides data on the sales team, including which region they operate in.

1. Find the top 5 cities where sales have declined the most compared to the previous month.

Answer:

WITH MonthlySales AS (
    SELECT 
        c.city,
        EXTRACT(MONTH FROM s.sale_date) AS sale_month,
        SUM(s.total_amount) AS monthly_sales
    FROM 
        Sales s
    JOIN 
        Customers c ON s.customer_id = c.customer_id
    WHERE 
        s.sale_date >= CURRENT_DATE - INTERVAL '2 months'
    GROUP BY 
        c.city, EXTRACT(MONTH FROM s.sale_date)
)
SELECT 
    a.city, 
    (b.monthly_sales - a.monthly_sales) AS decline
FROM 
    MonthlySales a
JOIN 
    MonthlySales b ON a.city = b.city 
                  AND a.sale_month = b.sale_month - 1
WHERE 
    b.monthly_sales < a.monthly_sales
ORDER BY 
    decline DESC
LIMIT 5;

---

2. Find customers who consistently increased their monthly spending for at least 3 consecutive months.

Answer:

WITH MonthlySpending AS (
    SELECT 
        customer_id,
        EXTRACT(MONTH FROM sale_date) AS month,
        SUM(total_amount) AS monthly_spending
    FROM 
        Sales
    GROUP BY 
        customer_id, EXTRACT(MONTH FROM sale_date)
),
Growth AS (
    SELECT 
        customer_id,
        month,
        LAG(monthly_spending) OVER (PARTITION BY customer_id ORDER BY month) AS prev_month_spending,
        monthly_spending
    FROM 
        MonthlySpending
)
SELECT 
    customer_id
FROM 
    Growth
WHERE 
    monthly_spending > prev_month_spending
GROUP BY 
    customer_id
HAVING 
    COUNT(*) >= 3;

---

3. Identify salespersons who exceeded their monthly sales targets consecutively for the last 6 months.

Answer:

WITH SalesTargets AS (
    SELECT 
        salesperson_id,
        EXTRACT(MONTH FROM sale_date) AS month,
        SUM(total_amount) AS total_sales
    FROM 
        Sales
    GROUP BY 
        salesperson_id, EXTRACT(MONTH FROM sale_date)
),
ConsistentAchievers AS (
    SELECT 
        salesperson_id,
        month,
        total_sales,
        LAG(total_sales, 1) OVER (PARTITION BY salesperson_id ORDER BY month) AS last_month_sales
    FROM 
        SalesTargets
)
SELECT 
    salesperson_id
FROM 
    ConsistentAchievers
WHERE 
    total_sales > last_month_sales
GROUP BY 
    salesperson_id
HAVING 
    COUNT(*) = 6;

---

4. Calculate the moving average of monthly sales for each product over the last 3 months.

Answer:

SELECT 
    product_id,
    EXTRACT(MONTH FROM sale_date) AS month,
    AVG(total_amount) OVER (PARTITION BY product_id ORDER BY EXTRACT(MONTH FROM sale_date) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_sales
FROM 
    Sales
ORDER BY 
    product_id, month;

---

5. Determine which products have seen a drop in sales volume by at least 30% over the last two quarters.

Answer:

WITH QuarterlySales AS (
    SELECT 
        product_id,
        EXTRACT(QUARTER FROM sale_date) AS quarter,
        SUM(quantity) AS total_quantity
    FROM 
        Sales
    GROUP BY 
        product_id, EXTRACT(QUARTER FROM sale_date)
)
SELECT 
    a.product_id
FROM 
    QuarterlySales a
JOIN 
    QuarterlySales b ON a.product_id = b.product_id AND a.quarter = b.quarter + 1
WHERE 
    a.total_quantity < b.total_quantity * 0.7;

---

6. Find the most frequently purchased product pairs by customers within a single transaction.

Answer:

SELECT 
    p1.product_id AS product_1, 
    p2.product_id AS product_2, 
    COUNT(*) AS pair_count
FROM 
    Sales s1
JOIN 
    Sales s2 ON s1.customer_id = s2.customer_id 
             AND s1.sale_id = s2.sale_id 
             AND s1.product_id < s2.product_id
JOIN 
    Products p1 ON s1.product_id = p1.product_id
JOIN 
    Products p2 ON s2.product_id = p2.product_id
GROUP BY 
    p1.product_id, p2.product_id
ORDER BY 
    pair_count DESC
LIMIT 5;

---

7. Identify sales trends by comparing sales growth rates for each quarter across different years.

Answer:

SELECT 
    EXTRACT(YEAR FROM sale_date) AS year,
    EXTRACT(QUARTER FROM sale_date) AS quarter,
    SUM(total_amount) AS total_sales,
    LAG(SUM(total_amount)) OVER (PARTITION BY EXTRACT(QUARTER FROM sale_date) ORDER BY EXTRACT(YEAR FROM sale_date)) AS previous_year_sales,
    ((SUM(total_amount) - LAG(SUM(total_amount)) OVER (PARTITION BY EXTRACT(QUARTER FROM sale_date) ORDER BY EXTRACT(YEAR FROM sale_date))) / LAG(SUM(total_amount)) OVER (PARTITION BY EXTRACT(QUARTER FROM sale_date) ORDER BY EXTRACT(YEAR FROM sale_date))) * 100 AS growth_rate
FROM 
    Sales
GROUP BY 
    EXTRACT(YEAR FROM sale_date), EXTRACT(QUARTER FROM sale_date)
ORDER BY 
    year, quarter;

---

8. Calculate the average sales per customer over their first three transactions.

Answer:

WITH RankedSales AS (
    SELECT 
        customer_id,
        sale_id,
        total_amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY sale_date) AS txn_rank
    FROM 
        Sales
)
SELECT 
    customer_id,
    AVG(total_amount) AS avg_first_three_sales
FROM 
    RankedSales
WHERE 
    txn_rank <= 3
GROUP BY 
    customer_id;

---

9. Identify products that contribute to at least 60% of the total sales revenue.

Answer:

WITH TotalRevenue AS (
    SELECT 
        SUM(total_amount) AS overall_sales
    FROM 
        Sales
),
ProductRevenue AS (
    SELECT 
        product_id,
        SUM(total_amount) AS product_sales,
        SUM(total_amount) / (SELECT overall_sales FROM TotalRevenue) AS contribution
    FROM 
        Sales
    GROUP BY 
        product_id
)
SELECT 
    product_id
FROM 
    ProductRevenue
WHERE 
    contribution >= 0.60;

---

10. Find the top 5 customers whose purchasing behaviour has changed significantly compared to the previous year.

Answer:

WITH YearlySales AS (
    SELECT 
        customer_id,
        EXTRACT(YEAR FROM sale_date) AS year,
        SUM(total_amount) AS total_sales
    FROM 
        Sales
    GROUP BY 
        customer_id, EXTRACT(YEAR FROM sale_date)
),
ChangeRate AS (
    SELECT 
        a.customer_id,
        a.total_sales AS current_year_sales,
        b.total_sales AS last_year_sales,
        ((a.total_sales - b.total_sales) / b.total_sales) * 100 AS change_percentage
    FROM 
        YearlySales a
    JOIN 
        YearlySales b ON a.customer_id = b.customer_id AND a.year = b.year + 1
)
SELECT 
    customer_id, change_percentage
FROM 
    ChangeRate
ORDER BY 
    ABS(change_percentage) DESC
LIMIT 5;

---

11. Calculate the cumulative sales value for each product and show the rank based on cumulative sales.

Answer:

SELECT 
    product_id,
    sale_date,
    SUM(total_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS cumulative_sales,
    RANK() OVER (ORDER BY SUM(total_amount) OVER (PARTITION BY product_id ORDER BY sale_date) DESC) AS rank
FROM 
    Sales
ORDER BY 
    product_id, sale_date;

• Mastering PostgreSQL Architecture: 6 Powerful Steps to Optimize Performance & Scalability
• Understanding Oracle Triggers – A Comprehensive Guide with Examples
• SQL Query Design Patterns: Writing Efficient Queries
• Master Oracle Interview Questions: Key Concepts & Examples for 2024
• Ace Your PL/SQL Interview Questions for Success! Part-4