Grouping and Filtering

1. Query the number of registered products per brand. Sort by p

Query the number of registered products per brand. Sort by product count in descending order.

Hint 1: Use GROUP BY brand and COUNT(*)

SELECT brand, COUNT(*) AS product_count
FROM products
GROUP BY brand
ORDER BY product_count DESC;

2. Query the number of customers per grade (tier).

Query the number of customers per grade (tier).

Hint 1: Use GROUP BY grade and COUNT(*)

SELECT grade, COUNT(*) AS customer_count
FROM customers
GROUP BY grade
ORDER BY customer_count DESC;

3. Query the number of orders and total revenue (sum of total_a

Query the number of orders and total revenue (sum of total_amount) per order status.

Hint 1: Use GROUP BY status, COUNT(*), and SUM(total_amount) together

SELECT
    status,
    COUNT(*) AS order_count,
    ROUND(SUM(total_amount), 0) AS total_revenue
FROM orders
GROUP BY status
ORDER BY total_revenue DESC;

4. Query how many reviews exist for each rating (1-5). Sort by

Query how many reviews exist for each rating (1-5). Sort by rating.

Hint 1: Use GROUP BY rating and COUNT(*)

SELECT rating, COUNT(*) AS review_count
FROM reviews
GROUP BY rating
ORDER BY rating;

5. Query the number of payments and total amount (sum of amount

Query the number of payments and total amount (sum of amount) per payment method. Sort by total amount in descending order.

Hint 1: Use GROUP BY method and SUM(amount)

SELECT
    method,
    COUNT(*) AS payment_count,
    ROUND(SUM(amount), 0) AS total_amount
FROM payments
GROUP BY method
ORDER BY total_amount DESC;

6. Query the number of complaints per submission channel. Sort

Query the number of complaints per submission channel. Sort by count in descending order.

Hint 1: Group the channel column of the complaints table using GROUP BY

SELECT channel, COUNT(*) AS complaint_count
FROM complaints
GROUP BY channel
ORDER BY complaint_count DESC;

7. Query the number of customers and average point balance (poi

Query the number of customers and average point balance (point_balance) per acquisition channel (acquisition_channel).

Hint 1: Use GROUP BY acquisition_channel and AVG(point_balance). Cases where acquisition_channel is NULL will appear as a separate group

SELECT
    acquisition_channel,
    COUNT(*) AS customer_count,
    ROUND(AVG(point_balance), 0) AS avg_points
FROM customers
GROUP BY acquisition_channel
ORDER BY avg_points DESC;

8. Extract the year from the order date (ordered_at) and query

Extract the year from the order date (ordered_at) and query the number of orders and total revenue per year.

Hint 1: Extract the year with SUBSTR(ordered_at, 1, 4) and group with GROUP BY

SELECT
    SUBSTR(ordered_at, 1, 4) AS year,
    COUNT(*) AS order_count,
    ROUND(SUM(total_amount), 0) AS total_revenue
FROM orders
GROUP BY SUBSTR(ordered_at, 1, 4)
ORDER BY year;

9. Query the average selling price (price) and average cost (co

Query the average selling price (price) and average cost (cost_price) per brand. Sort by average price in descending order.

Hint 1: Use GROUP BY brand with AVG(price) and AVG(cost_price) together

SELECT
    brand,
    ROUND(AVG(price), 0) AS avg_price,
    ROUND(AVG(cost_price), 0) AS avg_cost
FROM products
GROUP BY brand
ORDER BY avg_price DESC;

10. Query the number of complaints and average response count (r

Query the number of complaints and average response count (response_count) per complaint category.

Hint 1: Use GROUP BY category and AVG(response_count)

SELECT
    category,
    COUNT(*) AS complaint_count,
    ROUND(AVG(response_count), 1) AS avg_responses
FROM complaints
GROUP BY category
ORDER BY complaint_count DESC;

11. Calculate the product count per brand, then query only brand

Calculate the product count per brand, then query only brands with 10 or more registered products.

Hint 1: To filter groups after GROUP BY, use HAVING instead of WHERE

SELECT brand, COUNT(*) AS product_count
FROM products
GROUP BY brand
HAVING COUNT(*) >= 10
ORDER BY product_count DESC;

12. Calculate the average order amount per order status, then qu

Calculate the average order amount per order status, then query only statuses with an average of 300,000 or more.

Hint 1: Filter with HAVING AVG(total_amount) >= 300000

SELECT
    status,
    COUNT(*) AS order_count,
    ROUND(AVG(total_amount), 0) AS avg_amount
FROM orders
GROUP BY status
HAVING AVG(total_amount) >= 300000
ORDER BY avg_amount DESC;

13. Aggregate the monthly order count for 2024, and query only m

Aggregate the monthly order count for 2024, and query only months with 3,000 or more orders.

Hint 1: Filter 2024 first with WHERE, then aggregate by month with GROUP BY, and filter the count with HAVING

SELECT
    SUBSTR(ordered_at, 1, 7) AS month,
    COUNT(*) AS order_count
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY SUBSTR(ordered_at, 1, 7)
HAVING COUNT(*) >= 3000
ORDER BY month;

14. Query the count for each combination of complaint priority a

Query the count for each combination of complaint priority and status.

Hint 1: You can list multiple columns separated by commas in GROUP BY: GROUP BY priority, status

SELECT
    priority,
    status,
    COUNT(*) AS cnt
FROM complaints
GROUP BY priority, status
ORDER BY priority, status;

15. Query the customer count for each combination of gender and

Query the customer count for each combination of gender and grade (tier). Sort by customer count in descending order.

Hint 1: Group both columns together with GROUP BY gender, grade. Cases where gender is NULL will also appear as a separate group

SELECT
    gender,
    grade,
    COUNT(*) AS customer_count
FROM customers
GROUP BY gender, grade
ORDER BY customer_count DESC;

16. Filter only payments with status 'completed', then query pay

Filter only payments with status 'completed', then query payment methods with an average amount of 200,000 or more.

Hint 1: Filter with WHERE status = 'completed' first, then filter the average amount with GROUP BY + HAVING

SELECT
    method,
    COUNT(*) AS payment_count,
    ROUND(AVG(amount), 0) AS avg_amount
FROM payments
WHERE status = 'completed'
GROUP BY method
HAVING AVG(amount) >= 200000
ORDER BY avg_amount DESC;

17. For products currently on sale (is_active = 1), query brands

For products currently on sale (is_active = 1), query brands with a total stock (sum of stock_qty) of 100 or more.

Hint 1: Filter active products with WHERE is_active = 1, then apply GROUP BY brand + HAVING SUM(stock_qty) >= 100

SELECT
    brand,
    COUNT(*) AS product_count,
    SUM(stock_qty) AS total_stock
FROM products
WHERE is_active = 1
GROUP BY brand
HAVING SUM(stock_qty) >= 100
ORDER BY total_stock DESC;

18. Extract the year from the order date and query the order cou

Extract the year from the order date and query the order count for each year and status combination.

Hint 1: Group by both criteria together with GROUP BY SUBSTR(ordered_at, 1, 4), status

SELECT
    SUBSTR(ordered_at, 1, 4) AS year,
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY SUBSTR(ordered_at, 1, 4), status
ORDER BY year, order_count DESC;

19. Aggregate the review count per product (product_id), and que

Aggregate the review count per product (product_id), and query only products with 50 or more reviews.

Hint 1: Use GROUP BY product_id + HAVING COUNT(*) >= 50

SELECT
    product_id,
    COUNT(*) AS review_count
FROM reviews
GROUP BY product_id
HAVING COUNT(*) >= 50
ORDER BY review_count DESC;

20. Calculate the customer count per grade and acquisition_chann

Calculate the customer count per grade and acquisition_channel combination, and query only combinations with 100 or more customers.

Hint 1: After GROUP BY grade, acquisition_channel, filter with HAVING COUNT(*) >= 100

SELECT
    grade,
    acquisition_channel,
    COUNT(*) AS customer_count
FROM customers
GROUP BY grade, acquisition_channel
HAVING COUNT(*) >= 100
ORDER BY grade, customer_count DESC;

21. Divide the 2024 orders by quarter and query the order count,

Divide the 2024 orders by quarter and query the order count, total revenue, and average order amount. Quarters are determined by month.

Hint 1: Extract the month with SUBSTR(ordered_at, 6, 2), then calculate the quarter with (month - 1) / 3 + 1

SELECT
    (CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) - 1) / 3 + 1 AS quarter,
    COUNT(*) AS order_count,
    ROUND(SUM(total_amount), 0) AS total_revenue,
    ROUND(AVG(total_amount), 0) AS avg_amount
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY (CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) - 1) / 3 + 1
ORDER BY quarter;

22. For each complaint category, calculate the total count, esca

For each complaint category, calculate the total count, escalation count (escalated = 1), and escalation rate (%). Show only categories with a rate of 10% or more.

Hint 1: Calculate the escalation count with SUM(escalated), and the rate with 100.0 * SUM(escalated) / COUNT(*)

SELECT
    category,
    COUNT(*) AS total_count,
    SUM(escalated) AS escalated_count,
    ROUND(100.0 * SUM(escalated) / COUNT(*), 1) AS escalation_rate
FROM complaints
GROUP BY category
HAVING 100.0 * SUM(escalated) / COUNT(*) >= 10
ORDER BY escalation_rate DESC;

23. For card payments only (method = 'card'), query the payment

For card payments only (method = 'card'), query the payment count and total amount per card issuer (card_issuer). Sort by total amount in descending order.

Hint 1: Filter card payments with WHERE method = 'card', then aggregate with GROUP BY card_issuer

SELECT
    card_issuer,
    COUNT(*) AS payment_count,
    ROUND(SUM(amount), 0) AS total_amount
FROM payments
WHERE method = 'card'
GROUP BY card_issuer
ORDER BY total_amount DESC;

24. Calculate the average rating per product, then query product

Calculate the average rating per product, then query products with 5 or more reviews and an average rating below 3.0.

Hint 1: You can use multiple conditions in HAVING: HAVING COUNT(*) >= 5 AND AVG(rating) < 3.0

SELECT
    product_id,
    COUNT(*) AS review_count,
    ROUND(AVG(rating), 2) AS avg_rating
FROM reviews
GROUP BY product_id
HAVING COUNT(*) >= 5 AND AVG(rating) < 3.0
ORDER BY avg_rating;

25. Query the order count per year-month combination, but only f

Query the order count per year-month combination, but only for 2023 and 2024 data. Sort by year, then month.

Hint 1: Filter 2023 and 2024 with WHERE, then group by year and month with GROUP BY SUBSTR(ordered_at, 1, 4), SUBSTR(ordered_at, 6, 2)

SELECT
    SUBSTR(ordered_at, 1, 4) AS year,
    SUBSTR(ordered_at, 6, 2) AS month,
    COUNT(*) AS order_count
FROM orders
WHERE SUBSTR(ordered_at, 1, 4) IN ('2023', '2024')
GROUP BY SUBSTR(ordered_at, 1, 4), SUBSTR(ordered_at, 6, 2)
ORDER BY year, month;

26. Query the customer count and average point balance per grade

Query the customer count and average point balance per grade and is_active flag combination. Sort by grade, then is_active flag.

Hint 1: Combine both criteria with GROUP BY grade, is_active

SELECT
    grade,
    is_active,
    COUNT(*) AS customer_count,
    ROUND(AVG(point_balance), 0) AS avg_points
FROM customers
GROUP BY grade, is_active
ORDER BY grade, is_active DESC;

27. Query the count and average response count per channel and p

Query the count and average response count per channel and priority combination, showing only combinations with 50+ complaints and an average response count of 2 or more.

Hint 1: Combine two conditions with AND in HAVING: HAVING COUNT(*) >= 50 AND AVG(response_count) >= 2

SELECT
    channel,
    priority,
    COUNT(*) AS complaint_count,
    ROUND(AVG(response_count), 1) AS avg_responses
FROM complaints
GROUP BY channel, priority
HAVING COUNT(*) >= 50 AND AVG(response_count) >= 2
ORDER BY avg_responses DESC;

28. Find the average margin rate (%) per brand and show the top

Find the average margin rate (%) per brand and show the top 5 brands. The margin rate is calculated as (price - cost_price) / price * 100. Only include active products (is_active = 1).

Hint 1: Calculate the average margin rate per brand with AVG((price - cost_price) / price * 100). Extract the top 5 with ORDER BY + LIMIT

SELECT
    brand,
    COUNT(*) AS product_count,
    ROUND(AVG((price - cost_price) / price * 100), 1) AS avg_margin_pct
FROM products
WHERE is_active = 1
GROUP BY brand
ORDER BY avg_margin_pct DESC
LIMIT 5;

29. For card payments (method = 'card'), calculate the payment c

For card payments (method = 'card'), calculate the payment count and average amount per installment period (installment_months). Show only installment periods with 100 or more payments.

Hint 1: Filter with WHERE method = 'card', then apply GROUP BY installment_months + HAVING COUNT(*) >= 100

SELECT
    installment_months,
    COUNT(*) AS payment_count,
    ROUND(AVG(amount), 0) AS avg_amount
FROM payments
WHERE method = 'card'
GROUP BY installment_months
HAVING COUNT(*) >= 100
ORDER BY installment_months;

30. Extract the year from the customer signup date (created_at)

Extract the year from the customer signup date (created_at) and query the number of new signups per year. Show only years with 100 or more signups.

Hint 1: Extract the year with SUBSTR(created_at, 1, 4), then filter with GROUP BY + HAVING COUNT(*) >= 100

SELECT
    SUBSTR(created_at, 1, 4) AS year,
    COUNT(*) AS new_customers
FROM customers
GROUP BY SUBSTR(created_at, 1, 4)
HAVING COUNT(*) >= 100
ORDER BY year;