Comprehensive Exercises

1. Query the name, brand, and price of the top 5 most expensive

Query the name, brand, and price of the top 5 most expensive active products (is_active = 1).

Hint 1: This is a combination of WHERE filtering + ORDER BY DESC + LIMIT.

SELECT name, brand, price
FROM products
WHERE is_active = 1
ORDER BY price DESC
LIMIT 5;

2. Query the name and last login date of VIP-tier customers who

Query the name and last login date of VIP-tier customers who have a login history, sorted by most recent, limited to 10 rows.

Hint 1: Combine two conditions with WHERE grade = 'VIP' AND last_login_at IS NOT NULL.

SELECT name, last_login_at
FROM customers
WHERE grade = 'VIP'
  AND last_login_at IS NOT NULL
ORDER BY last_login_at DESC
LIMIT 10;

3. Query the order number, order amount, and cancellation date

Query the order number, order amount, and cancellation date of cancelled orders in 2024, sorted by most recent cancellation, limited to 10 rows.

Hint 1: Combine conditions with WHERE ordered_at LIKE '2024%' AND cancelled_at IS NOT NULL.

SELECT order_number, total_amount, cancelled_at
FROM orders
WHERE ordered_at LIKE '2024%'
  AND cancelled_at IS NOT NULL
ORDER BY cancelled_at DESC
LIMIT 10;

4. Find the count and average rating of reviews with a rating o

Find the count and average rating of reviews with a rating of 4 or higher. Display the average to 2 decimal places.

Hint 1: Filter with WHERE rating >= 4, then use COUNT(*) and ROUND(AVG(rating), 2).

SELECT COUNT(*) AS high_rating_count,
       ROUND(AVG(rating), 2) AS avg_rating
FROM reviews
WHERE rating >= 4;

5. Find the number of active products per brand, showing only b

Find the number of active products per brand, showing only brands with 10 or more, sorted by product count descending.

Hint 1: This is a combination of WHERE is_active = 1 + GROUP BY brand + HAVING COUNT(*) >= 10.

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

6. Find the average points per customer tier, displaying the ti

Find the average points per customer tier, displaying the tier in Korean. Sort by average points descending.

Hint 1: This is a combination of CASE for tier conversion + GROUP BY + AVG + ORDER BY.

SELECT CASE grade
           WHEN 'VIP' THEN 'VIP'
           WHEN 'GOLD' THEN '골드'
           WHEN 'SILVER' THEN '실버'
           WHEN 'BRONZE' THEN '브론즈'
       END AS grade_kr,
       ROUND(AVG(point_balance)) AS avg_points
FROM customers
GROUP BY grade
ORDER BY avg_points DESC;

7. Find the total payment amount and count per payment method,

Find the total payment amount and count per payment method, showing only methods with a total of 1 billion or more.

Hint 1: This is a combination of GROUP BY method + HAVING SUM(amount) >= 1000000000.

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

8. Find the count per tier for customers with a NULL signup cha

Find the count per tier for customers with a NULL signup channel. Use CASE to sort tiers in order starting from VIP.

Hint 1: This is a combination of WHERE acquisition_channel IS NULL + GROUP BY grade + ORDER BY CASE.

SELECT grade, COUNT(*) AS customer_count
FROM customers
WHERE acquisition_channel IS NULL
GROUP BY grade
ORDER BY CASE grade
             WHEN 'VIP' THEN 1
             WHEN 'GOLD' THEN 2
             WHEN 'SILVER' THEN 3
             WHEN 'BRONZE' THEN 4
         END;

9. Find the count, average order amount, and delivery notes com

Find the count, average order amount, and delivery notes completion rate (%) per order status. Sort by count descending.

Hint 1: Calculate the completion rate with COUNT(notes) * 100.0 / COUNT(*). This leverages the relationship between NULL and aggregate functions.

SELECT status,
       COUNT(*) AS order_count,
       ROUND(AVG(total_amount)) AS avg_amount,
       ROUND(COUNT(notes) * 100.0 / COUNT(*), 1) AS notes_rate_pct
FROM orders
GROUP BY status
ORDER BY order_count DESC;

10. Classify products by stock status (Out of stock/Low/Normal/S

Classify products by stock status (Out of stock/Low/Normal/Sufficient) and find the product count and average price per group. Active products only.

Hint 1: This is a combination of WHERE is_active = 1 + CASE for stock classification + GROUP BY + aggregate functions.

SELECT CASE
           WHEN stock_qty = 0 THEN '품절'
           WHEN stock_qty <= 10 THEN '부족'
           WHEN stock_qty <= 100 THEN '보통'
           ELSE '충분'
       END AS stock_status,
       COUNT(*) AS product_count,
       ROUND(AVG(price)) AS avg_price
FROM products
WHERE is_active = 1
GROUP BY stock_status
ORDER BY product_count DESC;

11. Find the active product count, average price, and average ma

Find the active product count, average price, and average margin rate (%) per brand. Show only brands with 5+ products, sorted by average margin rate descending.

Hint 1: Margin rate = (price - cost_price) * 100.0 / price. This is a combination of WHERE + GROUP BY + HAVING + ORDER BY.

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

12. Find the order count, total revenue, average order amount, a

Find the order count, total revenue, average order amount, and cancellation count per year. The cancellation count is the number of rows where cancelled_at is not NULL.

Hint 1: Extract the year with SUBSTR(ordered_at, 1, 4). COUNT(cancelled_at) counts only non-NULL rows.

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

13. Find the customer count, VIP rate (%), and average points pe

Find the customer count, VIP rate (%), and average points per signup channel. Display 'Unclassified' for NULL channels, sorted by customer count descending.

Hint 1: This is a combination of COALESCE for NULL replacement + SUM(CASE WHEN grade = 'VIP' THEN 1 ELSE 0 END) for VIP count aggregation + GROUP BY.

SELECT COALESCE(acquisition_channel, '미분류') AS channel,
       COUNT(*) AS customer_count,
       ROUND(SUM(CASE WHEN grade = 'VIP' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS vip_rate_pct,
       ROUND(AVG(point_balance)) AS avg_points
FROM customers
GROUP BY COALESCE(acquisition_channel, '미분류')
ORDER BY customer_count DESC;

14. Find the monthly order count and average order amount for 20

Find the monthly order count and average order amount for 2024, also displaying the season (Spring/Summer/Fall/Winter).

Hint 1: This is a combination of SUBSTR for month extraction + CASE for season classification + GROUP BY + aggregation.

SELECT SUBSTR(ordered_at, 1, 7) AS month,
       CASE
           WHEN CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) IN (3, 4, 5) THEN '봄'
           WHEN CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) IN (6, 7, 8) THEN '여름'
           WHEN CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) IN (9, 10, 11) THEN '가을'
           ELSE '겨울'
       END AS season,
       COUNT(*) AS order_count,
       ROUND(AVG(total_amount)) AS avg_amount
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY month
ORDER BY month;

15. Aggregate reviews by rating, displaying the Korean label, co

Aggregate reviews by rating, displaying the Korean label, count, overall percentage (%), and title completion rate (%) for each rating. Sort by rating descending.

Hint 1: This is a combination of CASE for rating labels + COUNT(*) + percentage calculation + COUNT(title).

SELECT rating,
       CASE rating
           WHEN 5 THEN '최고'
           WHEN 4 THEN '좋음'
           WHEN 3 THEN '보통'
           WHEN 2 THEN '별로'
           WHEN 1 THEN '최악'
       END AS rating_label,
       COUNT(*) AS review_count,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct,
       ROUND(COUNT(title) * 100.0 / COUNT(*), 1) AS title_rate_pct
FROM reviews
GROUP BY rating
ORDER BY rating DESC;

16. Find the product count, average stock, and discontinuation r

Find the product count, average stock, and discontinuation rate (%) per price tier (Budget/Mid-low/Mid/Premium). The discontinuation rate is the percentage of is_active=0.

Hint 1: This is a combination of CASE for price tier classification + SUM(CASE WHEN is_active = 0 THEN 1 ELSE 0 END) for discontinuation count + GROUP BY.

SELECT CASE
           WHEN price < 100000 THEN '저가'
           WHEN price < 500000 THEN '중저가'
           WHEN price < 1000000 THEN '중가'
           ELSE '고가'
       END AS price_tier,
       COUNT(*) AS product_count,
       ROUND(AVG(stock_qty)) AS avg_stock,
       ROUND(SUM(CASE WHEN is_active = 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS discontinued_pct
FROM products
GROUP BY price_tier
ORDER BY product_count DESC;

17. Query the customer count, average points, gender completion

Query the customer count, average points, gender completion rate (%), and login experience rate (%) per tier in a single query. Tier order: VIP > GOLD > SILVER > BRONZE.

Hint 1: Use COUNT(gender) for gender completion count and COUNT(last_login_at) for login experience count. Use ORDER BY CASE for tier ordering.

SELECT grade,
       COUNT(*) AS customer_count,
       ROUND(AVG(point_balance)) AS avg_points,
       ROUND(COUNT(gender) * 100.0 / COUNT(*), 1) AS gender_rate_pct,
       ROUND(COUNT(last_login_at) * 100.0 / COUNT(*), 1) AS login_rate_pct
FROM customers
GROUP BY grade
ORDER BY CASE grade
             WHEN 'VIP' THEN 1
             WHEN 'GOLD' THEN 2
             WHEN 'SILVER' THEN 3
             WHEN 'BRONZE' THEN 4
         END;

18. Classify orders into 3 status groups (Processing/Completed/C

Classify orders into 3 status groups (Processing/Completed/Cancelled-Returned) and find the count, total revenue, average shipping fee, and points-used count per group.

Hint 1: Classify groups with CASE WHEN status IN (...). Count points-used orders with SUM(CASE WHEN point_used > 0 THEN 1 ELSE 0 END).

SELECT CASE
           WHEN status IN ('pending', 'paid', 'preparing') THEN '처리중'
           WHEN status IN ('shipped', 'delivered', 'confirmed') THEN '완료'
           ELSE '취소/반품'
       END AS status_group,
       COUNT(*) AS order_count,
       ROUND(SUM(total_amount)) AS total_revenue,
       ROUND(AVG(shipping_fee)) AS avg_shipping,
       SUM(CASE WHEN point_used > 0 THEN 1 ELSE 0 END) AS point_used_count
FROM orders
GROUP BY status_group
ORDER BY order_count DESC;

19. For card payments, find the payment count, average amount, a

For card payments, find the payment count, average amount, and installment usage rate (%) per card issuer. Exclude records where card issuer is NULL, and show only card issuers with 100+ payments.

Hint 1: Filter with WHERE method = 'card' AND card_issuer IS NOT NULL. Count installment payments with SUM(CASE WHEN installment_months > 0 THEN 1 ELSE 0 END).

SELECT card_issuer,
       COUNT(*) AS payment_count,
       ROUND(AVG(amount)) AS avg_amount,
       ROUND(SUM(CASE WHEN installment_months > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS installment_rate_pct
FROM payments
WHERE method = 'card'
  AND card_issuer IS NOT NULL
GROUP BY card_issuer
HAVING COUNT(*) >= 100
ORDER BY payment_count DESC;

20. Find the product count and active product rate (%) per suppl

Find the product count and active product rate (%) per supplier. Only include suppliers with 3+ products, sorted by active rate descending. Group by supplier_id.

Hint 1: This is a combination of GROUP BY supplier_id + HAVING COUNT(*) >= 3 + SUM(CASE WHEN is_active = 1 ...) for active rate calculation + ORDER BY.

SELECT supplier_id,
       COUNT(*) AS product_count,
       SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) AS active_count,
       ROUND(SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS active_rate_pct
FROM products
GROUP BY supplier_id
HAVING COUNT(*) >= 3
ORDER BY active_rate_pct DESC;

21. Find the active product count and average price per brand, s

Find the active product count and average price per brand, showing only brands with an average price of 1M+. Also display the price tier classification (Premium/Mainstream/Budget).

Hint 1: This is a combination of WHERE is_active = 1 + GROUP BY brand + HAVING AVG(price) >= 1000000 + CASE for price tier classification.

SELECT brand,
       COUNT(*) AS active_product_count,
       ROUND(AVG(price)) AS avg_price,
       CASE
           WHEN AVG(price) >= 2000000 THEN '프리미엄'
           WHEN AVG(price) >= 1000000 THEN '대중'
           ELSE '보급형'
       END AS brand_tier
FROM products
WHERE is_active = 1
GROUP BY brand
HAVING AVG(price) >= 1000000
ORDER BY avg_price DESC;

22. 2024 monthly revenue analysis: Find the monthly order count,

2024 monthly revenue analysis: Find the monthly order count, total revenue, average order amount, cancellation rate (%), and free shipping rate (%).

Hint 1: Combine various aggregations. Cancellation rate = COUNT(cancelled_at) / COUNT(*). Free shipping = percentage of orders with shipping_fee = 0.

SELECT SUBSTR(ordered_at, 1, 7) AS month,
       COUNT(*) AS order_count,
       ROUND(SUM(total_amount)) AS total_revenue,
       ROUND(AVG(total_amount)) AS avg_amount,
       ROUND(COUNT(cancelled_at) * 100.0 / COUNT(*), 1) AS cancel_rate_pct,
       ROUND(SUM(CASE WHEN shipping_fee = 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS free_ship_pct
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY month
ORDER BY month;

23. Customer segment analysis: Combine tier and activity status

Customer segment analysis: Combine tier and activity status (Active/Dormant/Withdrawn) to find the customer count and average points per segment. Show only segments with 100+ customers.

Hint 1: Classify activity status with CASE (is_active=0 is Withdrawn, last_login_at IS NULL is Dormant, the rest is Active). Combine with GROUP BY grade, activity_status.

SELECT grade,
       CASE
           WHEN is_active = 0 THEN '탈퇴'
           WHEN last_login_at IS NULL THEN '휴면'
           ELSE '활동'
       END AS activity_status,
       COUNT(*) AS customer_count,
       ROUND(AVG(point_balance)) AS avg_points
FROM customers
GROUP BY grade, activity_status
HAVING COUNT(*) >= 100
ORDER BY CASE grade
             WHEN 'VIP' THEN 1
             WHEN 'GOLD' THEN 2
             WHEN 'SILVER' THEN 3
             WHEN 'BRONZE' THEN 4
         END,
         customer_count DESC;

24. Product data quality report: Find the product count, descrip

Product data quality report: Find the product count, description missing rate (%), specs missing rate (%), and weight missing rate (%) per brand. Only include brands with 10+ products, sorted by the overall missing rate (average of all three) descending.

Hint 1: Calculate each column's missing rate with (COUNT(*) - COUNT(column)) * 100.0 / COUNT(*). Sort by the average of the three missing rates.

SELECT brand,
       COUNT(*) AS product_count,
       ROUND((COUNT(*) - COUNT(description)) * 100.0 / COUNT(*), 1) AS desc_missing_pct,
       ROUND((COUNT(*) - COUNT(specs)) * 100.0 / COUNT(*), 1) AS specs_missing_pct,
       ROUND((COUNT(*) - COUNT(weight_grams)) * 100.0 / COUNT(*), 1) AS weight_missing_pct
FROM products
GROUP BY brand
HAVING COUNT(*) >= 10
ORDER BY (
    (COUNT(*) - COUNT(description)) +
    (COUNT(*) - COUNT(specs)) +
    (COUNT(*) - COUNT(weight_grams))
) * 1.0 / COUNT(*) DESC;

25. Yearly customer signup analysis: Find the customer count, ge

Yearly customer signup analysis: Find the customer count, gender ratio (Male/Female/Unknown), and average points per signup year.

Hint 1: Calculate the male ratio with SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) * 100.0 / COUNT(*). Also calculate the NULL gender ratio separately.

SELECT SUBSTR(created_at, 1, 4) AS join_year,
       COUNT(*) AS customer_count,
       ROUND(SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS male_pct,
       ROUND(SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS female_pct,
       ROUND(SUM(CASE WHEN gender IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS unknown_pct,
       ROUND(AVG(point_balance)) AS avg_points
FROM customers
GROUP BY join_year
ORDER BY join_year;

26. Discount analysis: For 2024 orders, combine discount status

Discount analysis: For 2024 orders, combine discount status (Has discount/No discount) and amount tier (Small/Regular/Large/VIP-level) to find the count and average order amount.

Hint 1: Classify discount status with CASE WHEN discount_amount > 0 and amount tier with CASE WHEN total_amount < 50000. Use both CASE expressions in GROUP BY.

SELECT CASE WHEN discount_amount > 0 THEN '할인있음' ELSE '할인없음' END AS has_discount,
       CASE
           WHEN total_amount < 50000 THEN '소액'
           WHEN total_amount < 200000 THEN '일반'
           WHEN total_amount < 1000000 THEN '고액'
           ELSE 'VIP급'
       END AS amount_tier,
       COUNT(*) AS order_count,
       ROUND(AVG(total_amount)) AS avg_amount
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY has_discount, amount_tier
ORDER BY has_discount, avg_amount DESC;

27. Product status by category: Find the total product count, ac

Product status by category: Find the total product count, active count, discontinued count, out-of-stock count, and average price per category_id. Only include categories with 5+ total products.

Hint 1: Use SUM(CASE WHEN condition THEN 1 ELSE 0 END) to aggregate each status count into separate columns.

SELECT category_id,
       COUNT(*) AS total,
       SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) AS active_count,
       SUM(CASE WHEN is_active = 0 THEN 1 ELSE 0 END) AS discontinued_count,
       SUM(CASE WHEN stock_qty = 0 THEN 1 ELSE 0 END) AS out_of_stock,
       ROUND(AVG(price)) AS avg_price
FROM products
GROUP BY category_id
HAVING COUNT(*) >= 5
ORDER BY total DESC;

28. Comprehensive payment method analysis: Find the count, total

Comprehensive payment method analysis: Find the count, total amount, average amount, refund count, and refund rate (%) per method (displayed in Korean). Only include methods with 1,000+ payments.

Hint 1: This is a combination of CASE for Korean method labels + COUNT(refunded_at) for refund count + HAVING + ORDER BY.

SELECT CASE method
           WHEN 'card' THEN '신용카드'
           WHEN 'bank_transfer' THEN '계좌이체'
           WHEN 'virtual_account' THEN '가상계좌'
           WHEN 'kakao_pay' THEN '카카오페이'
           WHEN 'naver_pay' THEN '네이버페이'
           WHEN 'point' THEN '포인트'
       END AS method_kr,
       COUNT(*) AS payment_count,
       ROUND(SUM(amount)) AS total_amount,
       ROUND(AVG(amount)) AS avg_amount,
       COUNT(refunded_at) AS refund_count,
       ROUND(COUNT(refunded_at) * 100.0 / COUNT(*), 1) AS refund_rate_pct
FROM payments
GROUP BY method
HAVING COUNT(*) >= 1000
ORDER BY total_amount DESC;

29. Customer profile completeness analysis: Find the customer co

Customer profile completeness analysis: Find the customer count, percentage (%), average points, and VIP rate (%) per completeness score (0-4). Completeness = number of non-NULL columns among birth_date, gender, last_login_at, and acquisition_channel.

Hint 1: (column IS NOT NULL) returns 1 or 0 in SQLite. Adding all four gives the completeness score.

SELECT (birth_date IS NOT NULL)
     + (gender IS NOT NULL)
     + (last_login_at IS NOT NULL)
     + (acquisition_channel IS NOT NULL) AS completeness,
       COUNT(*) AS customer_count,
       ROUND(AVG(point_balance)) AS avg_points,
       ROUND(SUM(CASE WHEN grade = 'VIP' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS vip_rate_pct
FROM customers
GROUP BY completeness
ORDER BY completeness;

30. Comprehensive dashboard: Find the active product count, aver

Comprehensive dashboard: Find the active product count, average price, average margin rate (%), price tier classification, and low-stock product count (stock_qty <= 10) per brand from the products table. Only include brands with 5+ active products, sorted by active product count descending, limited to the top 10.

Hint 1: Use multiple aggregations and CASE simultaneously. This is a combination of WHERE is_active = 1 + GROUP BY brand + HAVING + ORDER BY + LIMIT.

SELECT brand,
       COUNT(*) AS active_count,
       ROUND(AVG(price)) AS avg_price,
       ROUND(AVG((price - cost_price) * 100.0 / price), 1) AS avg_margin_pct,
       CASE
           WHEN AVG(price) >= 1000000 THEN '프리미엄'
           WHEN AVG(price) >= 300000 THEN '대중'
           ELSE '보급형'
       END AS brand_tier,
       SUM(CASE WHEN stock_qty <= 10 THEN 1 ELSE 0 END) AS low_stock_count
FROM products
WHERE is_active = 1
GROUP BY brand
HAVING COUNT(*) >= 5
ORDER BY active_count DESC
LIMIT 10;