SQL Programming Resources

🚀 SQL Scenario-Based Interview Questions with Answers (Part 9)

💼 FAANG & Product Company SQL Interview Scenarios

📌 Question 81: Find Users Who Were Active on 3 Consecutive Days

Table: user_activity (user_id, activity_date)

WITH activity AS (

SELECT DISTINCT

user_id,

activity_date

FROM user_activity

),

groups AS (

SELECT

user_id,

activity_date,

activity_date -

ROW_NUMBER() OVER (

PARTITION BY user_id

ORDER BY activity_date

) * INTERVAL '1 day' AS grp

FROM activity

)

SELECT

user_id,

COUNT() AS consecutive_days

FROM groups

GROUP BY user_id, grp

HAVING COUNT() >= 3;

📌 Question 82: Find the Top 5 Selling Products in Each Category

Tables: products (product_id, category) sales (product_id, quantity)

WITH product_sales AS (

SELECT

p.category,

s.product_id,

SUM(s.quantity) AS total_quantity

FROM sales s

JOIN products p

ON s.product_id = p.product_id

GROUP BY p.category, s.product_id

)

SELECT *

FROM (

SELECT *,

DENSE_RANK() OVER (

PARTITION BY category

ORDER BY total_quantity DESC

) AS rnk

FROM product_sales

) t

WHERE rnk <= 5;

📌 Question 83: Find Customers Whose Latest Order Is Their Highest Value Order

Table: orders (customer_id, order_date, amount)

WITH customer_orders AS (

SELECT *,

ROW_NUMBER() OVER (

PARTITION BY customer_id

ORDER BY order_date DESC

) AS latest_order,

RANK() OVER (

PARTITION BY customer_id

ORDER BY amount DESC

) AS highest_order

FROM orders

)

SELECT

customer_id,

order_date,

amount

FROM customer_orders

WHERE latest_order = 1

AND highest_order = 1;

📌 Question 84: Calculate Rolling 30-Day Revenue

Table: sales (sale_date, amount)

SELECT

sale_date,

SUM(amount) OVER (

ORDER BY sale_date

RANGE BETWEEN INTERVAL '29 days' PRECEDING

AND CURRENT ROW

) AS rolling_30_day_revenue

FROM sales;

📌 Question 85: Find Products Purchased by Exactly One Customer

Table: orders (customer_id, product_id)

SELECT

product_id

FROM orders

GROUP BY product_id

HAVING COUNT(DISTINCT customer_id) = 1;

📌 Question 86: Find the Longest Inactive Period for Each Customer

Table: orders (customer_id, order_date)

WITH gaps AS (

SELECT

customer_id,

order_date,

order_date -

LAG(order_date) OVER (

PARTITION BY customer_id

ORDER BY order_date

) AS inactive_days

FROM orders

)

SELECT

customer_id,

MAX(inactive_days) AS longest_gap

FROM gaps

GROUP BY customer_id;

📌 Question 87: Calculate Revenue Share by Product Category

Tables: products (product_id, category) sales (product_id, amount)

SELECT

category,

SUM(amount) AS revenue,

ROUND(

100.0 * SUM(amount) /

SUM(SUM(amount)) OVER (),

2

) AS revenue_share

FROM sales s

JOIN products p

ON s.product_id = p.product_id

GROUP BY category

ORDER BY revenue DESC;

SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. Here are some key concepts to understand the basics of SQL:

1. Database: A database is a structured collection of data organized in tables, which consist of rows and columns.

2. Table: A table is a collection of related data organized in rows and columns. Each row represents a record, and each column represents a specific attribute or field.

3. Query: A SQL query is a request for data or information from a database. Queries are used to retrieve, insert, update, or delete data in a database.

4. CRUD Operations: CRUD stands for Create, Read, Update, and Delete. These are the basic operations performed on data in a database using SQL:
   - Create (INSERT): Adds new records to a table.
   - Read (SELECT): Retrieves data from one or more tables.
   - Update (UPDATE): Modifies existing records in a table.
   - Delete (DELETE): Removes records from a table.

5. Data Types: SQL supports various data types to define the type of data that can be stored in each column of a table, such as integer, text, date, and decimal.

6. Constraints: Constraints are rules enforced on data columns to ensure data integrity and consistency. Common constraints include:
   - Primary Key: Uniquely identifies each record in a table.
   - Foreign Key: Establishes a relationship between two tables.
   - Unique: Ensures that all values in a column are unique.
   - Not Null: Specifies that a column cannot contain NULL values.

7. Joins: Joins are used to combine rows from two or more tables based on a related column between them. Common types of joins include INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).

8. Aggregate Functions: SQL provides aggregate functions to perform calculations on sets of values. Common aggregate functions include SUM, AVG, COUNT, MIN, and MAX.

9. Group By: The GROUP BY clause is used to group rows that have the same values into summary rows. It is often used with aggregate functions to perform calculations on grouped data.

10. Order By: The ORDER BY clause is used to sort the result set of a query based on one or more columns in ascending or descending order.

Understanding these basic concepts of SQL will help you write queries to interact with databases effectively. Practice writing SQL queries and experimenting with different commands to become proficient in using SQL for database management and manipulation.

SQL Learning Series: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/1075

📌 Question 69: Find Orders Above Monthly Average

Table: orders (order_id, amount, order_date)

WITH monthly_avg AS (

    SELECT

        DATE_TRUNC('month', order_date) AS month,

        AVG(amount) AS avg_amount

    FROM orders

    GROUP BY DATE_TRUNC('month', order_date)

)

SELECT

    o.order_id,

    o.amount,

    o.order_date

FROM orders o

JOIN monthly_avg m

ON DATE_TRUNC('month', o.order_date) = m.month

WHERE o.amount > m.avg_amount;

📌 Question 70: Calculate Customer Repeat Rate by Month

Table: orders (customer_id, order_date)

WITH customer_orders AS (

    SELECT

        DATE_TRUNC('month', order_date) AS month,

        customer_id,

        COUNT() AS order_count

    FROM orders

    GROUP BY month, customer_id

)

SELECT

    month,

    ROUND(

        100.0 *

        COUNT(CASE WHEN order_count > 1 THEN 1 END)

        / COUNT(),

        2

    ) AS repeat_rate

FROM customer_orders

GROUP BY month

ORDER BY month;

🎯 Concepts Covered:

✅ Window Functions

✅ Streak Analysis

✅ Customer Segmentation

✅ Weekly & Monthly KPIs

✅ Revenue Analytics

✅ Business Intelligence

✅ Advanced Aggregations

✅ Real Interview Scenarios 

❤️ Double Tap For More

🚀 SQL Scenario-Based Interview Questions with Answers: Part-7

📌 Question 61: Find the Longest Purchase Streak

Table: orders (customer_id, order_date)

Requirement: Find the longest consecutive daily purchase streak for each customer.

WITH purchase_days AS (

SELECT DISTINCT

customer_id,

order_date

FROM orders

),

streaks AS (

SELECT

customer_id,

order_date,

order_date -

ROW_NUMBER() OVER (

PARTITION BY customer_id

ORDER BY order_date

) * INTERVAL '1 day' AS grp

FROM purchase_days

)

SELECT

customer_id,

COUNT(*) AS longest_streak

FROM streaks

GROUP BY customer_id, grp

ORDER BY longest_streak DESC;

📌 Question 62: Find Products Purchased by More Than 80% of Customers

Tables:

products (product_id)

orders (customer_id, product_id)

SELECT

product_id

FROM orders

GROUP BY product_id

HAVING COUNT(DISTINCT customer_id) >=

(

SELECT COUNT(DISTINCT customer_id) * 0.80

FROM orders

);

📌 Question 63: Find the Highest Revenue Day for Each Month

Table: sales (sale_date, amount)

WITH daily_sales AS (

SELECT

DATE(sale_date) AS sale_day,

SUM(amount) AS revenue

FROM sales

GROUP BY DATE(sale_date)

)

SELECT

month,

sale_day,

revenue

FROM (

SELECT

DATE_TRUNC('month', sale_day) AS month,

sale_day,

revenue,

DENSE_RANK() OVER (

PARTITION BY DATE_TRUNC('month', sale_day)

ORDER BY revenue DESC

) AS rnk

FROM daily_sales

) t

WHERE rnk = 1;

📌 Question 64: Calculate Customer Purchase Frequency

Table: orders (customer_id, order_date)

Requirement: Average number of days between consecutive orders.

WITH purchase_gap AS (

SELECT

customer_id,

order_date,

LAG(order_date) OVER (

PARTITION BY customer_id

ORDER BY order_date

) AS previous_order

FROM orders

)

SELECT

customer_id,

ROUND(

AVG(order_date - previous_order),

2

) AS avg_days_between_orders

FROM purchase_gap

WHERE previous_order IS NOT NULL

GROUP BY customer_id;

📌 Question 65: Find Customers Who Bought Only One Product Category

Tables:

orders (customer_id, product_id)

products (product_id, category)

SELECT

customer_id

FROM orders o

JOIN products p

ON o.product_id = p.product_id

GROUP BY customer_id

HAVING COUNT(DISTINCT category) = 1;

📌 Question 66: Calculate Revenue by Week

Table: sales (sale_date, amount)

SELECT

DATE_TRUNC('week', sale_date) AS week,

SUM(amount) AS revenue

FROM sales

GROUP BY DATE_TRUNC('week', sale_date)

ORDER BY week;

📌 Question 67: Find Customers with the Highest Average Order Value

Table: orders (customer_id, amount)

SELECT

customer_id,

ROUND(AVG(amount), 2) AS avg_order_value

FROM orders

GROUP BY customer_id

ORDER BY avg_order_value DESC

LIMIT 10;

📌 Question 68: Find the Month with the Highest Number of New Customers

Table: users (user_id, signup_date)

SELECT

DATE_TRUNC('month', signup_date) AS month,

COUNT(*) AS new_customers

FROM users

GROUP BY DATE_TRUNC('month', signup_date)

ORDER BY new_customers DESC

LIMIT 1;

🚀 SQL Business Scenario Interview Questions with Answers Part 6

📌 Question 51: Find Customers Who Haven't Ordered in the Last 90 Days

Tables: customers (customer_id), orders (customer_id, order_date)

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id
HAVING MAX(order_date) < CURRENT_DATE - INTERVAL '90 days'
    OR MAX(order_date) IS NULL;

📌 Question 52: Calculate Average Revenue Per User ARPU

Tables: users (user_id), orders (user_id, amount)

SELECT
ROUND(
    SUM(amount) * 1.0 /
    COUNT(DISTINCT u.user_id),
    2
) AS arpu
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id;

📌 Question 53: Find the Fastest Selling Product

Table: inventory (product_id, launch_date)
Table: sales (product_id, sale_date)

Requirement: Find the product with the fewest days from launch to first sale.

WITH first_sale AS (
SELECT
    product_id,
    MIN(sale_date) AS first_sale_date
FROM sales
GROUP BY product_id
)
SELECT
i.product_id,
(first_sale_date - launch_date) AS days_to_sell
FROM inventory i
JOIN first_sale f
ON i.product_id = f.product_id
ORDER BY days_to_sell
LIMIT 1;

📌 Question 54: Find Customers Who Purchased in Every Quarter

Table: orders (customer_id, order_date)

WITH customer_quarters AS (
SELECT
customer_id,
COUNT(
DISTINCT DATE_TRUNC('quarter', order_date)
) AS quarter_count
FROM orders
GROUP BY customer_id
),
total_quarters AS (
SELECT COUNT(
DISTINCT DATE_TRUNC('quarter', order_date)
) AS total_quarters
FROM orders
)
SELECT customer_id
FROM customer_quarters
CROSS JOIN total_quarters
WHERE quarter_count = total_quarters;

📌 Question 55: Find Revenue Contribution of Top 10 Customers

Table: orders (customer_id, amount)

WITH customer_revenue AS (
SELECT
customer_id,
SUM(amount) AS revenue
FROM orders
GROUP BY customer_id
)
SELECT
SUM(revenue) AS top10_revenue,
ROUND(
100.0 * SUM(revenue) /
(
SELECT SUM(amount)
FROM orders
),
2
) AS contribution_pct
FROM (
SELECT revenue
FROM customer_revenue
ORDER BY revenue DESC
LIMIT 10
) t;

📌 Question 56: Find Products Never Returned

Tables:
products (product_id)
sales (order_id, product_id)
returns (order_id)

SELECT DISTINCT
p.product_id
FROM products p
LEFT JOIN sales s
ON p.product_id = s.product_id
LEFT JOIN returns r
ON s.order_id = r.order_id
WHERE r.order_id IS NULL;

📌 Question 57: Calculate Daily Revenue Growth

Table: sales (sale_date, amount)

WITH daily_sales AS (
SELECT
sale_date,
SUM(amount) AS revenue
FROM sales
GROUP BY sale_date
)
SELECT
sale_date,
revenue,
ROUND(
100.0 *
(
revenue -
LAG(revenue) OVER(
ORDER BY sale_date
)
/
LAG(revenue) OVER(
ORDER BY sale_date
),
2
) AS growth_pct
FROM daily_sales;

📌 Question 58: Find the Most Loyal Customers

Table: orders (customer_id, order_date)

Requirement: Customers who placed orders in the highest number of distinct months.

SELECT
customer_id,
COUNT(
DISTINCT DATE_TRUNC('month', order_date)
) AS active_months
FROM orders
GROUP BY customer_id
ORDER BY active_months DESC
LIMIT 10;

📌 Question 59: Find Products With Zero Sales

Tables:
products (product_id)
sales (product_id)

SELECT
p.product_id
FROM products p
LEFT JOIN sales s
ON p.product_id = s.product_id
WHERE s.product_id IS NULL;

📌 Question 60: Calculate Average Orders Per Customer

Table: orders (customer_id)

SELECT
ROUND(
COUNT(*) * 1.0 /
COUNT(DISTINCT customer_id),
2
) AS avg_orders_per_customer
FROM orders;

❤️ Double Tap For More

🚀 SQL Scenario Based Interview Questions with Answers: Part-5

📌 Question 41: Find Customers Whose Spending Decreased for 3 Consecutive Months 

Table: orders (customer_id, amount, order_date)

WITH monthly_spend AS (

    SELECT

        customer_id,

        DATE_TRUNC('month', order_date) AS month,

        SUM(amount) AS revenue

    FROM orders

    GROUP BY customer_id, DATE_TRUNC('month', order_date)

),

spend_trend AS (

    SELECT *,

           LAG(revenue,1) OVER(PARTITION BY customer_id ORDER BY month) AS prev1,

           LAG(revenue,2) OVER(PARTITION BY customer_id ORDER BY month) AS prev2

    FROM monthly_spend

)

SELECT customer_id, month

FROM spend_trend

WHERE revenue < prev1

  AND prev1 < prev2;

📌 Question 42: Find the Median Order Amount for Each Month 

Table: orders (order_id, amount, order_date)

SELECT

    DATE_TRUNC('month', order_date) AS month,

    PERCENTILE_CONT(0.5)

        WITHIN GROUP (ORDER BY amount) AS median_order

FROM orders

GROUP BY DATE_TRUNC('month', order_date);

📌 Question 43: Find Customers Who Ordered on Every Weekend 

Table: orders (customer_id, order_date)

SELECT

    customer_id

FROM orders

WHERE EXTRACT(DOW FROM order_date) IN (0,6)

GROUP BY customer_id

HAVING COUNT(DISTINCT order_date) >= 8;

📌 Question 44: Find the Top 5% Highest Revenue Customers 

Table: orders (customer_id, amount)

WITH revenue AS (

    SELECT

        customer_id,

        SUM(amount) AS total_revenue

    FROM orders

    GROUP BY customer_id

)

SELECT *

FROM (

    SELECT *,

           NTILE(20) OVER (ORDER BY total_revenue DESC) AS bucket

    FROM revenue

) t

WHERE bucket = 1;

📌 Question 45: Find the Most Frequently Returned Product 

Tables:

sales (order_id, product_id)

returns (order_id)

SELECT

    s.product_id,

    COUNT(*) AS return_count

FROM sales s

JOIN returns r

ON s.order_id = r.order_id

GROUP BY s.product_id

ORDER BY return_count DESC

LIMIT 1;

📌 Question 46: Calculate Average Delivery Time 

Table: deliveries (order_id, order_date, delivery_date)

SELECT

    ROUND(

        AVG(delivery_date - order_date),

        2

    ) AS avg_delivery_days

FROM deliveries;

📌 Question 47: Find Users Who Logged In Every Day Last Week 

Table: logins (user_id, login_date)

SELECT

    user_id

FROM logins

WHERE login_date >= CURRENT_DATE - INTERVAL '6 day'

GROUP BY user_id

HAVING COUNT(DISTINCT login_date) = 7;

📌 Question 48: Find Products with Revenue Above Category Average 

Tables:

products (product_id, category)

sales (product_id, amount)

WITH product_revenue AS (

    SELECT

        p.product_id,

        p.category,

        SUM(s.amount) AS revenue

    FROM products p

    JOIN sales s

      ON p.product_id = s.product_id

    GROUP BY p.product_id, p.category

)

SELECT *

FROM (

    SELECT *,

           AVG(revenue) OVER (

               PARTITION BY category

           ) AS category_avg

    FROM product_revenue

) t

WHERE revenue > category_avg;

📌 Question 49: Find the Busiest Day of the Week 

Table: orders (order_date)

SELECT

    TO_CHAR(order_date, 'Day') AS weekday,

    COUNT(*) AS total_orders

FROM orders

GROUP BY weekday

ORDER BY total_orders DESC

LIMIT 1;

📌 Question 50: Calculate Customer Retention After First Purchase

Table: orders (customer_id, order_date)

WITH customer_orders AS (

    SELECT

        customer_id,

        COUNT() AS total_orders

    FROM orders

    GROUP BY customer_id

)

SELECT

    ROUND(

        100.0 *

        COUNT(CASE WHEN total_orders > 1 THEN 1 END)

        / COUNT(),

        2

    ) AS retention_rate

FROM customer_orders;

🎯 Concepts Covered:

✅ Window Functions

✅ Percentiles

✅ NTILE()

✅ Retention Analysis

✅ Revenue Analytics

✅ Delivery KPIs

✅ Customer Behavior Analysis

✅ Advanced Business SQL 

❤️ Double Tap For More

SELECT 
    EXTRACT(HOUR FROM order_timestamp) AS order_hour,
    COUNT(*) AS total_orders
FROM orders
GROUP BY order_hour
ORDER BY total_orders DESC
LIMIT 1;

📌 Question 40: Rank Salespersons by Quarterly Revenue

Table: sales salesperson_id, amount, sale_date

WITH quarterly_sales AS (
    SELECT 
        salesperson_id,
        DATE_TRUNC('quarter', sale_date) AS quarter,
        SUM(amount) AS revenue
    FROM sales
    GROUP BY 1,2
)
SELECT *,
    DENSE_RANK() OVER (
        PARTITION BY quarter 
        ORDER BY revenue DESC
    ) AS sales_rank
FROM quarterly_sales;

Double Tap ❤️ For More

🚀 SQL Scenario Based Interview Questions with Answers Part 4

📌 Question 31: Find Customers Who Increased Their Monthly Spending

Table: orders customer_id, amount, order_date

Requirement: Return customers whose spending increased compared to the previous month.

WITH monthly_spend AS (
    SELECT 
        customer_id,
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS total_spend
    FROM orders
    GROUP BY customer_id, DATE_TRUNC('month', order_date)
)
SELECT 
    customer_id,
    month,
    total_spend
FROM (
    SELECT *,
           LAG(total_spend) OVER (
               PARTITION BY customer_id 
               ORDER BY month
           ) AS prev_spend
    FROM monthly_spend
) t
WHERE total_spend > prev_spend;

📌 Question 32: Find Products Purchased Together Most Often

Table: order_items order_id, product_id

SELECT 
    a.product_id AS product_1,
    b.product_id AS product_2,
    COUNT(*) AS frequency
FROM order_items a
JOIN order_items b 
    ON a.order_id = b.order_id 
    AND a.product_id < b.product_id
GROUP BY 1,2
ORDER BY frequency DESC
LIMIT 10;

📌 Question 33: Find Users Active for 7 Consecutive Days

Table: user_activity user_id, activity_date

WITH activity AS (
    SELECT 
        user_id,
        activity_date,
        activity_date - 
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY activity_date
        ) * INTERVAL '1 day' AS grp
    FROM user_activity
)
SELECT 
    user_id
FROM activity
GROUP BY user_id, grp
HAVING COUNT(*) >= 7;

📌 Question 34: Calculate Repeat Purchase Rate

Table: orders customer_id, order_id

SELECT 
    ROUND(
        100.0 * 
        COUNT(CASE WHEN order_count > 1 THEN 1 END) / 
        COUNT(*), 
        2
    ) AS repeat_purchase_rate
FROM (
    SELECT 
        customer_id,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) t;

📌 Question 35: Find the Longest Gap Between Two Orders

Table: orders customer_id, order_date

WITH gaps AS (
    SELECT 
        customer_id,
        order_date,
        order_date - 
        LAG(order_date) OVER (
            PARTITION BY customer_id 
            ORDER BY order_date
        ) AS gap_days
    FROM orders
)
SELECT 
    customer_id,
    MAX(gap_days) AS longest_gap
FROM gaps
GROUP BY customer_id;

📌 Question 36: Calculate Revenue Lost Due to Churn

Tables: customers customer_id, status, orders customer_id, amount

SELECT 
    SUM(amount) AS churned_revenue
FROM customers c
JOIN orders o 
    ON c.customer_id = o.customer_id
WHERE c.status = 'Churned';

📌 Question 37: Find the Fastest Growing Product

Table: sales product_id, amount, sale_date

WITH monthly_sales AS (
    SELECT 
        product_id,
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount) AS revenue
    FROM sales
    GROUP BY 1,2
)
SELECT * 
FROM (
    SELECT *,
        revenue - 
        LAG(revenue) OVER (
            PARTITION BY product_id 
            ORDER BY month
        ) AS growth
    FROM monthly_sales
) t
ORDER BY growth DESC
LIMIT 1;

📌 Question 38: Find Customers Who Purchased Every Product Category

Tables: products product_id, category, orders customer_id, product_id

SELECT 
    customer_id
FROM orders o
JOIN products p 
    ON o.product_id = p.product_id
GROUP BY customer_id
HAVING COUNT(DISTINCT category) = (
    SELECT COUNT(DISTINCT category) 
    FROM products
);

📌 Question 39: Find Peak Ordering Hour

Table: orders order_id, order_timestamp

🚀 SQL Scenario Based Interview Questions with Answers: Part-3

📌 Question 21: Find the First Purchase Date for Every Customer

Table: orders order_id, customer_id, order_date

SELECT 
    customer_id,
    MIN(order_date) AS first_purchase_date
FROM orders
GROUP BY customer_id;

📌 Question 22: Calculate Customer Lifetime Value (CLV)

Table: orders customer_id, amount

Requirement: Total revenue generated by each customer.

SELECT 
    customer_id,
    SUM(amount) AS lifetime_value
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC;

📌 Question 23: Find the Top 5 Products Contributing 80% of Revenue

Table: sales product_id, amount

WITH product_revenue AS (
    SELECT 
        product_id,
        SUM(amount) AS revenue
    FROM sales
    GROUP BY product_id
),
revenue_rank AS (
    SELECT 
        product_id,
        revenue,
        SUM(revenue) OVER (ORDER BY revenue DESC) AS running_revenue,
        SUM(revenue) OVER () AS total_revenue
    FROM product_revenue
)
SELECT 
    product_id,
    revenue,
    ROUND(100.0 * running_revenue / total_revenue, 2) AS cumulative_pct
FROM revenue_rank
WHERE running_revenue <= total_revenue * 0.80;

📌 Question 24: Find Customers Who Purchased More Than 3 Different Products

Table: orders customer_id, product_id

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

📌 Question 25: Find the Highest Revenue Order for Each Customer

Table: orders order_id, customer_id, amount

WITH ranked_orders AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id 
               ORDER BY amount DESC
           ) AS rn
    FROM orders
)
SELECT 
    customer_id,
    order_id,
    amount
FROM ranked_orders
WHERE rn = 1;

📌 Question 26: Calculate Average Time Between Orders
Table: orders customer_id, order_date

WITH order_gap AS (
    SELECT 
        customer_id,
        order_date,
        LAG(order_date) OVER (
            PARTITION BY customer_id 
            ORDER BY order_date
        ) AS previous_order
    FROM orders
)
SELECT 
    customer_id,
    AVG(order_date - previous_order) AS avg_days_between_orders
FROM order_gap
WHERE previous_order IS NOT NULL
GROUP BY customer_id;

📌 Question 27: Find Users Who Abandoned Their Cart
Tables: cart user_id, product_id, orders user_id

Requirement: Users who added items to their cart but never completed a purchase.

SELECT DISTINCT 
    c.user_id
FROM cart c
LEFT JOIN orders o
    ON c.user_id = o.user_id
WHERE o.user_id IS NULL;

📌 Question 28: Find Revenue Generated by New vs Returning Customers

Tables: users user_id, signup_date, orders user_id, amount, order_date

SELECT 
    CASE 
        WHEN DATE_TRUNC('month', signup_date) = 
             DATE_TRUNC('month', order_date) 
        THEN 'New'
        ELSE 'Returning'
    END AS customer_type,
    SUM(amount) AS revenue
FROM users u
JOIN orders o 
    ON u.user_id = o.user_id
GROUP BY customer_type;

📌 Question 29: Find the Most Frequently Purchased Product Pair

Table: order_items order_id, product_id

SELECT 
    a.product_id AS product_1,
    b.product_id AS product_2,
    COUNT(*) AS pair_count
FROM order_items a
JOIN order_items b 
    ON a.order_id = b.order_id 
    AND a.product_id < b.product_id
GROUP BY 
    a.product_id, 
    b.product_id
ORDER BY pair_count DESC
LIMIT 1;

📌 Question 30: Calculate Revenue Contribution by Region

Tables: customers customer_id, region, orders customer_id, amount

SELECT 
    c.region,
    SUM(o.amount) AS revenue,
    ROUND(
        100.0 * SUM(o.amount) / 
        SUM(SUM(o.amount)) OVER (), 
        2
    ) AS revenue_share
FROM customers c
JOIN orders o 
    ON c.customer_id = o.customer_id
GROUP BY c.region
ORDER BY revenue DESC;

Double Tap ❤️ For More

𝗦𝗤𝗟 𝗠𝘂𝘀𝘁-𝗞𝗻𝗼𝘄 𝗗𝗶𝗳𝗳𝗲𝗿𝗲𝗻𝗰𝗲𝘀 📊

Whether you're writing daily queries or preparing for interviews, understanding these subtle SQL differences can make a big impact on both performance and accuracy.

🧠 Here’s a powerful visual that compares the most commonly misunderstood SQL concepts — side by side.

📌 𝗖𝗼𝘃𝗲𝗿𝗲𝗱 𝗶𝗻 𝘁𝗵𝗶𝘀 𝘀𝗻𝗮𝗽𝘀𝗵𝗼𝘁:
🔹 RANK() vs DENSE_RANK()
🔹 HAVING vs WHERE
🔹 UNION vs UNION ALL
🔹 JOIN vs UNION
🔹 CTE vs TEMP TABLE
🔹 SUBQUERY vs CTE
🔹 ISNULL vs COALESCE
🔹 DELETE vs DROP
🔹 INTERSECT vs INNER JOIN
🔹 EXCEPT vs NOT IN

React ♥️ for detailed post with examples

🎓 𝗚𝗼𝗼𝗴𝗹𝗲 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝟮𝟬𝟮𝟲 🚀

Learn job-ready skills from Google and boost your resume?🌟

✔️ Learn from Google Experts
✔️ Industry-Recognized Certificates
✔️ Beginner-Friendly Learning Paths
✔️ Self-Paced Courses
✔️ Enhance Resume & LinkedIn Profile
✔️ Build Job-Ready Skills

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:

https://pdlink.in/4vjLGVq

⏳ Start Learning Today & Upgrade Your Career!

✅ SQL Interview Questions with Answers

1. What is a window function? 
A window function computes results over a group ("window") of rows related to the current row, without collapsing them (like GROUP BY).

Examples: ROW_NUMBER(), RANK(), SUM() OVER(...) for running totals, rankings, or moving averages.

2. What is the difference between RANK() and ROW_NUMBER()? 
• ROW_NUMBER(): assigns unique sequential numbers to all rows, even if values are equal.
• RANK(): gives same rank to tied values, then skips the next rank (e.g., 1, 1, 3).

3. How do you find the second highest salary? 
SELECT salary 
FROM ( 
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk 
  FROM employees 
) t 
WHERE rnk = 2; 
This avoids ties if you want exactly the second‑highest value.

4. What is a recursive CTE? 
A recursive CTE refers to itself in its WITH definition, usually in the form "anchor + UNION ALL recursive step". It is used for hierarchical data like managers‑employees, org charts, or tree structures.

5. What is the difference between correlated and non-correlated subquery? 
• Non‑correlated: runs once, independent of the outer query.
• Correlated: references columns from the outer query and runs once per outer row (e.g., SELECT ... FROM t1 WHERE col > (SELECT AVG(col) FROM t2 WHERE t2.id = t1.id)).

6. How do you remove duplicates without DISTINCT? 
Use window functions: 
DELETE FROM ( 
  SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) as rn 
  FROM table 
) t 
WHERE rn > 1; 
Or use GROUP BY and keep one row per group.

7. What is an INDEX and when do you use it? 
An index speeds up data retrieval on specified columns (used in WHERE, JOIN, ORDER BY). Use it on columns that are frequently filtered or joined; avoid on very small tables or columns updated often.

8. Explain self-join with example. 
A self‑join joins a table to itself using aliases. Example: 
SELECT e1.name as employee, e2.name as manager 
FROM employees e1 
LEFT JOIN employees e2 ON e1.manager_id = e2.id; 
Useful for parent‑child relationships.

9. What is the difference between DELETE, DROP, and TRUNCATE? 
• DELETE: removes rows (can be filtered by WHERE), can be rolled back.
• TRUNCATE: removes all rows quickly, resets storage; often not logged per row.
• DROP: removes entire table (structure + data); cannot be rolled back.

10. How do you pivot/unpivot data in SQL? 
• Pivot: turns rows into columns (e.g., sales per month as columns) using PIVOT or conditional aggregation (MAX(CASE WHEN ... END)).
• Unpivot: turns columns into rows (e.g., multiple month columns → one month column) using UNPIVOT or UNION ALL/VALUES.

11. What is LAG() and LEAD()? 
• LAG(col, n): value of col from n rows before current row.
• LEAD(col, n): value from n rows after. Used for time‑series analysis (MoM change, prior/next values).

12. How do you handle NULL in aggregates? 
Most aggregates (SUM, AVG, MAX, MIN) ignore NULL. 
• COUNT(col) ignores NULL; COUNT(*) counts all rows.
• Use COALESCE() or ISNULL() to replace NULL before aggregating.

13. What is the difference between VIEW and MATERIALIZED VIEW? 
• VIEW: virtual table; query runs every time you select.
• MATERIALIZED VIEW: stores result physically and refreshes periodically; faster reads, slower updates.

14. Explain ACID properties. 
• Atomicity: transaction is "all or nothing".
• Consistency: valid state before and after.
• Isolation: concurrent transactions don't interfere.
• Durability: committed changes survive crashes.

15. How do you optimize a slow query? 
• Add proper indexes on WHERE, JOIN, ORDER BY columns.
• Remove unnecessary SELECT *, DISTINCT, or functions on indexed columns.
• Check execution plan and avoid large scans; use LIMIT or partitioning if possible.

16. What is the difference between INNER JOIN and EXISTS? 
• INNER JOIN: returns combined columns from both tables where keys match.
• EXISTS: checks if a subquery returns any rows; usually faster when you only care about existence (e.g., filtering with WHERE EXISTS).

📊 𝗧𝗖𝗦 𝗙𝗥𝗘𝗘 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀

Here's an amazing opportunity from TCS to learn essential data analytics skills completely FREE and earn a certificate

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:

https://pdlink.in/4waJYWJ

🔥 Data Analytics continues to be one of the most in-demand career paths, and this free course is a great first step toward building job-ready skills.

⏳ Don't miss this opportunity to upskill and boost your career!

Hi Guys,

Here are some of the telegram channels which may help you in data analytics journey 👇👇

SQL: https://t.me/sqlanalyst

Power BI & Tableau: https://t.me/PowerBI_analyst

Excel: https://t.me/excel_analyst

Python: https://t.me/dsabooks

Jobs: https://t.me/datasciencej

Data Science: https://t.me/datasciencefree

Artificial intelligence: https://t.me/aiindi

Data Analysts: https://t.me/sqlspecialist

Hope it helps :)

𝟳 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝗧𝗼 𝗘𝗻𝗿𝗼𝗹𝗹 𝗜𝗻 𝟮𝟬𝟮𝟲😍 

✅ 100% FREE & Beginner-Friendly
✅ Learn AI, ML, Data Science, Ethical Hacking & More
✅ Taught by Industry Experts
✅ Practical & Hands-on Learning

📢 Start learning today and take your tech career to the next level! 🚀

𝐋𝐢𝐧𝐤 👇:- 
 
https://pdlink.in/4bQ6FpS
 
Enroll For FREE & Get Certified 🎓

⚙️ Data Analytics Roadmap

📂 Excel/Google Sheets (VLOOKUP, Pivot Tables, Charts)
∟📂 SQL (SELECT, JOINs, GROUP BY, Window Functions)
∟📂 Python/R Basics (Pandas, Data Cleaning)
∟📂 Statistics (Descriptive, Inferential, Correlation)
∟📂 Data Visualization (Tableau, Power BI, Matplotlib)
∟📂 ETL Processes (Extract, Transform, Load)
∟📂 Dashboard Design (KPIs, Storytelling)
∟📂 Business Intelligence Tools (Looker, Metabase)
∟📂 Data Quality & Governance
∟📂 A/B Testing & Experimentation
∟📂 Advanced Analytics (Cohort Analysis, Funnel Analysis)
∟📂 Big Data Basics (Spark, Airflow)
∟📂 Communication (Reports, Presentations)
∟📂 Projects (Sales Dashboard, Customer Segmentation)
∟✅ Apply for Data Analyst / BI Analyst Roles

💬 Tap ❤️ for more!

𝗣𝗮𝘆 𝗔𝗳𝘁𝗲𝗿 𝗣𝗹𝗮𝗰𝗲𝗺𝗲𝗻𝘁 - 𝗙𝘂𝗹𝗹𝘀𝘁𝗮𝗰𝗸𝗗𝗲𝘃 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗪𝗶𝘁𝗵 𝗚𝗲𝗻𝗔𝗜 😍

Curriculum designed and taught by alumni from IITs & leading tech companies.

Learn Coding & Get Placed In Top Tech Companies

𝗛𝗶𝗴𝗵𝗹𝗶𝗴𝗵𝘁𝘀:-

💼 Avg. Package: ₹7.2 LPA | Highest: ₹41 LPA

𝐑𝐞𝐠𝐢𝐬𝐭𝐞𝐫 𝐍𝐨𝐰 👇:-

 https://pdlink.in/42WOE5H

Hurry! Limited seats are available.🏃‍♂️

✅SQL Roadmap: Step-by-Step Guide to Master SQL 🧠💻

Whether you're aiming to be a backend dev, data analyst, or full-time SQL pro — this roadmap has got you covered 👇

📍 1. SQL Basics
⦁  SELECT, FROM, WHERE
⦁  ORDER BY, LIMIT, DISTINCT 
   Learn data retrieval & filtering.

📍 2. Joins Mastery
⦁  INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN
⦁  SELF JOIN, CROSS JOIN 
   Master table relationships.

📍 3. Aggregate Functions
⦁  COUNT(), SUM(), AVG(), MIN(), MAX() 
   Key for reporting & analytics.

📍 4. Grouping Data
⦁  GROUP BY to group
⦁  HAVING to filter groups 
   Example: Sales by region, top categories.

📍 5. Subqueries & Nested Queries
⦁  Use subqueries in WHERE, FROM, SELECT
⦁  Use EXISTS, IN, ANY, ALL 
   Build complex logic without extra joins.

📍 6. Data Modification
⦁  INSERT INTO, UPDATE, DELETE
⦁  MERGE (advanced) 
   Safely change dataset content.

📍 7. Database Design Concepts
⦁  Normalization (1NF to 3NF)
⦁  Primary, Foreign, Unique Keys 
   Design scalable, clean DBs.

📍 8. Indexing & Query Optimization
⦁  Speed queries with indexes
⦁  Use EXPLAIN, ANALYZE to tune 
   Vital for big data/enterprise work.

📍 9. Stored Procedures & Functions
⦁  Reusable logic, control flow (IF, CASE, LOOP) 
   Backend logic inside the DB.

📍 10. Transactions & Locks
⦁  ACID properties
⦁  BEGIN, COMMIT, ROLLBACK
⦁  Lock types (SHARED, EXCLUSIVE) 
   Prevent data corruption in concurrency.

📍 11. Views & Triggers
⦁  CREATE VIEW for abstraction
⦁  TRIGGERS auto-run SQL on events 
   Automate & maintain logic.

📍 12. Backup & Restore
⦁  Backup/restore with tools (mysqldump, pg_dump) 
   Keep your data safe.

📍 13. NoSQL Basics (Optional)
⦁  Learn MongoDB, Redis basics
⦁  Understand where SQL ends & NoSQL begins.

📍 14. Real Projects & Practice
⦁  Build projects: Employee DB, Sales Dashboard, Blogging System
⦁  Practice on LeetCode, StrataScratch, HackerRank

📍 15. Apply for SQL Dev Roles
⦁  Tailor resume with projects & optimization skills
⦁  Prepare for interviews with SQL challenges
⦁  Know common business use cases

💡 Pro Tip: Combine SQL with Python or Excel to boost your data career options.

💬 Double Tap ♥️ For More!

𝗔𝗰𝗰𝗲𝗻𝘁𝘂𝗿𝗲 𝗙𝗥𝗘𝗘 𝗩𝗶𝗿𝘁𝘂𝗮𝗹 𝗜𝗻𝘁𝗲𝗿𝗻𝘀𝗵𝗶𝗽 𝗳𝗼𝗿 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝘄𝗶𝘁𝗵 𝗙𝗿𝗲𝗲 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗲 📊

Join the Accenture Virtual Internship Program and learn industry-relevant analytics skills with a free certificate 🌍

✨ Learn from Accenture Industry Experts
✨ Boost Your Resume & LinkedIn Profile
✨ Gain Practical Analytics Experience
✨ Improve Career Opportunities in 2026
✨ Great for Students & Freshers

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:

https://pdlink.in/42TuhXg

🔥 Start your Data Analytics journey today and gain valuable virtual internship experience from a top global company.