SQL Mastery — Module 1: aggregation

Total questions: **50**

Use the included SQLite database to solve the questions.

Beginner

1. Count the total number of customers.

2. Count the total number of products.

3. Count the total number of orders.

4. Find the average product price.

5. Find the highest product price.

6. Find the lowest product price.

7. Find the total quantity sold.

8. Find the total revenue from order_items.

9. Count how many unique countries customers come from.

10. Count how many unique product categories exist.

Easy

11. Count customers per country.

12. Count customers per city.

13. Count products per category.

14. Count orders by status.

15. Find average product price per category.

16. Find total quantity sold per order.

17. Find total revenue per order.

18. Find total quantity sold per product.

19. Find total revenue per product.

20. Find total revenue per product category.

Intermediate

21. Find countries with more than 19 customers.

22. Find cities with more than 6 customers.

23. Find categories with average price greater than 350.

24. Find orders with total revenue greater than 10,000.

25. Find products that sold more than 134 units.

26. Find customers who placed more than 6 orders.

27. Find the percentage of orders that are Completed.

28. Find the percentage of orders that are Cancelled.

29. Find the percentage of customers from Canada.

30. Find each category’s percentage share of total revenue.

Advanced

31. Find average revenue per customer.

32. Find average order value.

33. Find average quantity per order.

34. Find the customer with the highest total revenue.

35. Find the product with the highest total revenue.

36. Find the category with the highest total revenue.

37. Find the country with the highest total revenue.

38. Find revenue from Completed orders only.

39. Find percentage of revenue from Electronics.

40. Find percentage of customers who placed at least one order.

Interview Style

41. Which customers have revenue above the average customer revenue?

42. Which products generated revenue above the average product revenue?

43. Which category contributes the largest share of revenue?

44. Which order has the highest number of items?

45. Which city has the highest average customer revenue?

46. Find customers who have placed orders but only have Completed orders.

47. Find the cancellation rate by country.

48. Find revenue by month.

49. Find the best-performing month by revenue.

50. Find the percentage contribution of each customer to total revenue.
