SQL Programming Resources

📌 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;

𝗧𝗖𝗦 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗢𝗻 𝗗𝗮𝘁𝗮 𝗠𝗮𝗻𝗮𝗴𝗲𝗺𝗲𝗻𝘁 - 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘😍 TCS iON is offering a FREE Master Data Management Course with a Certificate, ✅ 100% FREE Learning ✅ Certificate on Completion ✅ Self-Paced Online Course ✅ Beginner-Friendly Content ✅ Industry-Relevant Skills ✅ Resume & LinkedIn Profile Boost 🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇: https://pdlink.in/4jGFBw0 🚀 Start Learning Today. Upskill for Free. Get Career Ready!

🚀 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

📊 𝗙𝗥𝗘𝗘 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 | 𝗡𝗼 𝗘𝘅𝗽𝗲𝗿𝗶𝗲𝗻𝗰𝗲 𝗡𝗲𝗲𝗱𝗲𝗱! 🚀 Want to start a career in Data Analytics but don't know where to begin? These 5 FREE beginner-friendly courses will help you learn the most in-demand data skills and build a strong foundation. 🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇: https://pdlink.in/3SOk64h 🚀 Start Learning Today. Build Your Portfolio. Land Your Dream Data Job!

🚀 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

📊 𝗙𝗥𝗘𝗘 𝗧𝗮𝘁𝗮 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗩𝗶𝗿𝘁𝘂𝗮𝗹 𝗜𝗻𝘁𝗲𝗿𝗻𝘀𝗵𝗶𝗽 | 𝗪𝗶𝘁𝗵 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗲 🚀 Here's an amazing opportunity to complete the FREE Tata Data Analytics Virtual Internship and earn a certificate that you can showcase on your Resume and LinkedIn. ✅ 100% FREE ✅ Self-Paced & Online ✅ Beginner-Friendly ✅ Certificate on Completion ✅ Real Business Case Studies ✅ Resume & LinkedIn Boost 🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇: https://pdlink.in/4eybW8J 🚀 Upskill Today. Build Your Portfolio. Get Career Ready!

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

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 ----- 1.38 ₽ · /balance_help

🚀 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

📊 𝗣𝘄𝗖 𝗶𝘀 𝗼𝗳𝗳𝗲𝗿𝗶𝗻𝗴 𝗮 𝗙𝗥𝗘𝗘 𝗣𝗼𝘄𝗲𝗿 𝗕𝗜 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗣𝗿𝗼𝗴𝗿𝗮𝗺 This helps tolearn data visualization, dashboard creation, KPI analysis, and business intelligence skills that companies actively look for. ✅ Free Certificate ✅ Self-Paced Learning ✅ Hands-On Power BI Projects ✅ Beginner Friendly ✅ Resume & LinkedIn Boost Don't miss this opportunity to add an in-demand skill to your profile and stand out from the crowd! 💼🔥 🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇: https://pdlink.in/4g5sKFa Share with yours friends who wants to start a career in Data Analytics

🚀 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

𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝟭𝟬𝟬+ 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝗳𝗼𝗿 𝗔𝘇𝘂𝗿𝗲, 𝗔𝗜, 𝗖𝘆𝗯𝗲𝗿𝘀𝗲𝗰𝘂𝗿𝗶𝘁𝘆 & 𝗠𝗼𝗿𝗲 🚀 Learn the most in-demand tech skills from Microsoft completely FREE🌟 Microsoft Learn offers 100+ free courses designed to help students, freshers, and professionals build job-ready skills in today's fastest-growing technology domains. ✅ 100% Free Learning ✅ Beginner to Advanced Levels 🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇: https://pdlink.in/4f0GNuH 🚀 Learn. Practice. Upskill. Get Career Ready

𝗦𝗤𝗟 𝗠𝘂𝘀𝘁-𝗞𝗻𝗼𝘄 𝗗𝗶𝗳𝗳𝗲𝗿𝗲𝗻𝗰𝗲𝘀 📊 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!