SQL Archives - Learn DA-DS Course in Hindi

E-Commerce SQL Question Problem:You are tasked with analyzing the top 3 customers based on their total spending in the last 6 months. For each customer, you need to provide the following details:Customer's IDCustomer's Name (First and Last)Total Amount SpentNumber of Orders PlacedTotal Quantity of Items PurchasedThe result should only include customers who have placed at least 3 orders in the last 6 months.Tables Involved:customer tableorders tableorder_items tableproduct table (for getting prices)Example customer data: customer _id first _name last _name phone _number shipping _address billing _address created _at 1 John Doe 123-456-7890 123 Elm St, City, Country 123 Elm St, City, Country 2025-01-01 10:00:00 2 Jane Smith 987-654-3210 456 Oak St, City, Country 456 Oak St, City, Country 2025-01-05 14:00:00 product _id product _name description price stock _quantity category created _at 1 Laptop 15-inch screen laptop 799.99 50 Electronics 2025-01-01 10:00:00 2 Headphones Noise-canceling headphones 199.99 100 Electronics 2025-01-03 12:00:00 3 Coffee Maker Automatic coffee machine 59.99 200 Kitchen 2025-01-02 15:00:00 Relationship Between the customer and product Tables The two tables, customer and product, are independent in this schema. Example orders and order_items data: orders table: order_id customer_id order_date total_amount 1 1 2025-01-01 10:30:00 999.98 2 2 2025-01-05 14:30:00 459.98 order_items table: order_item_id order_id product_id quantity item_price 1 1 1 1 799.99 2 1 2 1 199.99 3 2 3 2 59.99 WITH CustomerSpending AS ( -- Calculate the total spending, number of orders, and total quantity per customer SELECT o.customer_id, c.first_name, c.last_name, SUM(oi.quantity * oi.item_price) AS total_spent, COUNT(DISTINCT o.order_id) AS total_orders, SUM(oi.quantity) AS total_quantity FROM orders o JOIN order_items oi ON o.order_id = oi.order_id JOIN customer c ON o.customer_id = c.customer_id WHERE o.order_date >= CURRENT_DATE - INTERVAL '6 MONTH' -- Orders placed in the last 6 months GROUP BY o.customer_id, c.first_name, c.last_name HAVING COUNT(DISTINCT o.order_id) >= 3 -- Only consider customers with at least 3 orders ) -- Select the top 3 customers based on total spending SELECT customer_id, first_name, last_name, total_spent, total_orders, total_quantity FROM CustomerSpending ORDER BY total_spent DESC -- Order by the highest spending LIMIT 3; -- Top 3 customers Explanation: CustomerSpending CTE: This Common Table Expression (CTE) calculates the total amount spent (total_spent), number of orders (total_orders), and total quantity of items purchased (total_quantity) for each customer within the last 6 months. The WHERE clause filters orders within the last 6 months (CURRENT_DATE - INTERVAL '6 MONTH'). The HAVING clause ensures that only customers who placed at least 3 orders are included in the result. Main Query: After calculating the spending details in the CTE, the main query selects the top 3 customers based on their total spending (total_spent). The ORDER BY total_spent DESC orders the customers by their total spending in descending order, and LIMIT 3 fetches only the top 3 customers. Sample Output: customer _id first _name last _name total _spent total _orders total _quantity 5 John Doe 2500.50 5 15 2 Jane Smith 1890.75 4 12 3 Alice Johnson 1750.20 3 10 Comment form Your Comment * Your Name * Your Email * Post Comment If you are human, leave this field blank. Recent Posts AI Learning Deloitte SQL interview questions Amazon SQL interview questions Deloitte SQL interview questions 2 Recent Post SQL Amazon SQL interview questions Arpit verma January 15, 2025 Read More SQL Deloitte SQL interview questions 2 Arpit verma January 15, 2025 Read More SQL Deloitte SQL interview questions Arpit verma January 14, 2025 Read More AI AI Learning Arpit verma January 13, 2025 Read More

Category: SQL

Amazon SQL interview questions

Deloitte SQL interview questions 2

Deloitte SQL interview questions

Resourses

Quick Links

Support