SQL Query Showcase

Copy

/*1. Select all records from the sales table.*/
SELECT * FROM sales_data;


/*2. Calculate total amount spent across all customers.*/
SELECT SUM(total_amount) AS total_revenue FROM sales_data;


/*3. Find number of customers in each city.*/
SELECT city, COUNT(DISTINCT customer_id) AS customer_count 
FROM sales_data GROUP BY city;


/*4. Retrieve customers with 'High' income.*/
SELECT customer_id, name, income 
FROM sales_data 
WHERE Income = 'High';


/*5. Calculate average purchases by payment method.*/
SELECT payment_method, AVG(Total_purchases) AS average_purchase 
FROM sales_data 
GROUP BY Payment_Method;


/*6. Find total sales grouped by product category.*/
SELECT Product_Category, SUM(Total_Amount) AS total_sales
FROM sales_data 
GROUP BY Product_Category;


/*7. List customers who have made more than 10 purchases.*/
SELECT customer_id, name, COUNT(*) AS purchase_count
FROM sales_data 
GROUP BY Customer_ID, name 
HAVING COUNT(*) > 10;


/*8. Retrieve orders with status 'Delivered'.*/
SELECT order_id, customer_name, order_status FROM sales_data 
WHERE Order_Status = 'Delivered';


/*9. Calculate total revenue generated per product brand.*/
SELECT Product_Brand, SUM(total_amount) AS brand_revenue
FROM sales_data 
GROUP BY Product_Brand;


/*10. Find most popular shipping method.*/
SELECT shipping_method, COUNT(*) AS method_count 
FROM sales_data 
GROUP BY shipping_method 
ORDER BY method_count DESC 
LIMIT 1;


/*11. Calculate average age of customers per customer segment.*/
SELECT customer_segment, AVG(age) AS average_age
FROM sales_data 
GROUP BY customer_segment;


/*12. Retrieve all orders placed via 'PayPal'.*/
SELECT order_id, customer_name, payment_method FROM sales_data 
WHERE payment_method = 'PayPal';


/*13. Count number of orders per order status*/
SELECT order_status, COUNT(*) AS order_count
FROM sales_data 
GROUP BY order_status;


/*14. Find total quantity sold for each product type*/
SELECT product_type, SUM(total_purchases) AS total_quantity_sold
FROM sales_data 
GROUP BY product_type;


/*15. Calculate total amount generated per city and sort descending.*/
SELECT city, SUM(total_amount) AS total_amount_per_city 
FROM sales_data 
GROUP BY city 
ORDER BY total_amount_per_city DESC;


/*16. Retrieve customers with a feedback rating below 3.*/
SELECT customer_id, name, ratings 
FROM sales_data 
WHERE ratings < 3;


/*17. Find total amount spent by gender.*/
SELECT gender, SUM(total_amount) AS total_spent
FROM sales_data 
GROUP BY gender;


/*18. List customers with highest total purchases.*/
SELECT customer_id, name, total_purchases 
FROM sales_data 
ORDER BY total_purchases DESC
LIMIT 10;