Aggregate and GROUP BY Challenges — 8 Problems

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

SELECT c.customer_id, c.first_name, c.last_name,
       COUNT(o.order_id) AS total_orders,
       SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC
LIMIT 5;

SELECT p.category,
       COUNT(DISTINCT p.product_id) AS total_products,
       COALESCE(SUM(oi.quantity), 0) AS units_sold,
       COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue,
       ROUND(AVG(r.rating), 1) AS avg_rating
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN reviews r ON p.product_id = r.product_id
GROUP BY p.category
ORDER BY revenue DESC;

SELECT
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  COUNT(*) AS order_count,
  SUM(total_amount) AS monthly_revenue,
  ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

SELECT city, COUNT(*) AS customer_count
FROM customers
GROUP BY city
HAVING COUNT(*) > 2
ORDER BY customer_count DESC;

SELECT payment_method,
       COUNT(*) AS transaction_count,
       SUM(amount) AS total_amount,
       ROUND(AVG(amount), 2) AS avg_transaction,
       MIN(amount) AS smallest_payment,
       MAX(amount) AS largest_payment
FROM payments
WHERE payment_status = 'success'
GROUP BY payment_method
ORDER BY total_amount DESC;

SELECT p.product_id, p.product_name, p.category,
       COUNT(r.review_id) AS review_count,
       ROUND(AVG(r.rating), 1) AS avg_rating,
       MIN(r.rating) AS worst_rating,
       MAX(r.rating) AS best_rating
FROM products p
JOIN reviews r ON p.product_id = r.product_id
GROUP BY p.product_id, p.product_name, p.category
HAVING AVG(r.rating) < 4
ORDER BY avg_rating ASC;

SELECT
  CASE
    WHEN order_count = 1 THEN 'One-time'
    WHEN order_count BETWEEN 2 AND 3 THEN 'Regular'
    ELSE 'Loyal'
  END AS customer_segment,
  COUNT(*) AS customer_count
FROM (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
) AS customer_orders
GROUP BY
  CASE
    WHEN order_count = 1 THEN 'One-time'
    WHEN order_count BETWEEN 2 AND 3 THEN 'Regular'
    ELSE 'Loyal'
  END
ORDER BY customer_count DESC;