-- Correct: WHERE filters rows before grouping, HAVING filters groups after
SELECT status, COUNT(*) AS cnt, SUM(total) AS revenue
FROM orders
WHERE created_at >= '2024-01-01'   -- Step 2: remove old rows first
GROUP BY status                     -- Step 3: group remaining rows
HAVING revenue > 10000             -- Step 4: keep only high-revenue groups
ORDER BY revenue DESC;             -- Step 6: sort

-- Count orders per status
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status;

-- Sum revenue per customer
SELECT customer_id, ROUND(SUM(total), 2) AS total_spent
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
ORDER BY total_spent DESC;

-- Check the current SQL mode
SELECT @@sql_mode;

-- ERROR in ONLY_FULL_GROUP_BY mode:
-- 'first_name' is not in GROUP BY and is not an aggregate
SELECT customer_id, first_name, COUNT(*) AS orders
FROM orders
JOIN customers USING (customer_id)
GROUP BY customer_id;
-- ERROR 1055: Expression #2 of SELECT list is not in GROUP BY clause

-- Fix Option 1: add first_name to GROUP BY
SELECT customer_id, first_name, COUNT(*) AS orders
FROM orders
JOIN customers USING (customer_id)
GROUP BY customer_id, first_name
ORDER BY orders DESC;

-- Fix Option 2: use ANY_VALUE() to explicitly pick one value (MySQL 5.7+)
-- Use when you know the value is the same for all rows in the group
SELECT customer_id, ANY_VALUE(first_name) AS first_name, COUNT(*) AS orders
FROM orders
JOIN customers USING (customer_id)
GROUP BY customer_id;

-- Fix Option 3: use an aggregate that makes the intent clear
SELECT customer_id, MIN(first_name) AS first_name, COUNT(*) AS orders
FROM orders
JOIN customers USING (customer_id)
GROUP BY customer_id;

-- Revenue per country AND per status
SELECT
  country,
  status,
  COUNT(*)                 AS orders,
  ROUND(SUM(o.total), 2)   AS revenue
FROM orders AS o
JOIN customers AS c USING (customer_id)
GROUP BY country, status
ORDER BY country, status;

-- Group by calendar month
SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS month,
  COUNT(*)                          AS orders,
  ROUND(SUM(total), 2)              AS revenue
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;

-- Group by week using YEARWEEK
SELECT
  YEARWEEK(created_at, 1)           AS iso_week,
  MIN(DATE(created_at))             AS week_start,
  COUNT(*)                          AS orders
FROM orders
GROUP BY YEARWEEK(created_at, 1)
ORDER BY iso_week;

-- Group by price bucket using CASE
SELECT
  CASE
    WHEN price < 25              THEN 'Budget (under $25)'
    WHEN price BETWEEN 25 AND 99 THEN 'Mid-range ($25-$99)'
    ELSE                              'Premium ($100+)'
  END                            AS price_tier,
  COUNT(*)                       AS product_count,
  ROUND(AVG(price), 2)           AS avg_price
FROM products
WHERE is_active = 1
GROUP BY price_tier
ORDER BY avg_price;

-- Pivot: monthly revenue broken down by product category
SELECT
  DATE_FORMAT(o.created_at, '%Y-%m')                                        AS month,
  ROUND(SUM(CASE WHEN c.name = 'Electronics' THEN oi.qty * oi.unit_price END), 2) AS electronics,
  ROUND(SUM(CASE WHEN c.name = 'Clothing'    THEN oi.qty * oi.unit_price END), 2) AS clothing,
  ROUND(SUM(CASE WHEN c.name = 'Books'       THEN oi.qty * oi.unit_price END), 2) AS books,
  ROUND(SUM(oi.qty * oi.unit_price), 2)                                     AS total
FROM orders      AS o
JOIN order_items AS oi  ON o.order_id    = oi.order_id
JOIN products    AS p   ON oi.product_id = p.product_id
JOIN categories  AS c   ON p.category_id = c.category_id
WHERE o.status = 'delivered'
GROUP BY DATE_FORMAT(o.created_at, '%Y-%m')
ORDER BY month;

-- Count orders by status using conditional aggregation
SELECT
  DATE(created_at)                               AS date,
  COUNT(*)                                       AS total,
  SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending,
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY date;

-- Sales by category and product — with category subtotals + grand total
SELECT
  COALESCE(cat.name, 'ALL CATEGORIES') AS category,
  COALESCE(p.name,   'SUBTOTAL')       AS product,
  COUNT(DISTINCT o.order_id)           AS orders,
  ROUND(SUM(oi.qty * oi.unit_price), 2) AS revenue
FROM orders      AS o
JOIN order_items AS oi  ON o.order_id    = oi.order_id
JOIN products    AS p   ON oi.product_id = p.product_id
JOIN categories  AS cat ON p.category_id = cat.category_id
WHERE o.status = 'delivered'
GROUP BY cat.name, p.name WITH ROLLUP
ORDER BY cat.name, p.name;

-- GROUPING() function (MySQL 8.0+): returns 1 for ROLLUP-generated NULL rows
-- Distinguishes "real NULL" from "ROLLUP summary NULL"
SELECT
  CASE GROUPING(cat.name) WHEN 1 THEN 'GRAND TOTAL' ELSE cat.name END AS category,
  CASE GROUPING(p.name)   WHEN 1 THEN 'SUBTOTAL'    ELSE p.name   END AS product,
  ROUND(SUM(oi.qty * oi.unit_price), 2)                                AS revenue
FROM orders      AS o
JOIN order_items AS oi  ON o.order_id    = oi.order_id
JOIN products    AS p   ON oi.product_id = p.product_id
JOIN categories  AS cat ON p.category_id = cat.category_id
WHERE o.status = 'delivered'
GROUP BY cat.name, p.name WITH ROLLUP;

-- DO NOT rely on GROUP BY for ordering
SELECT status, COUNT(*) AS cnt
FROM orders
GROUP BY status;
-- Order of rows is NOT guaranteed

-- Always add explicit ORDER BY
SELECT status, COUNT(*) AS cnt
FROM orders
GROUP BY status
ORDER BY cnt DESC;

-- Index that supports GROUP BY + WHERE + ORDER BY
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- Check if the query uses the index
EXPLAIN
SELECT status, COUNT(*), SUM(total)
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY status;
-- Look for: 'Using index' or 'Using index condition' in Extra column
-- Avoid: 'Using filesort' or 'Using temporary' on large tables

-- Composite index for multi-column GROUP BY
ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status);

EXPLAIN
SELECT customer_id, status, COUNT(*)
FROM orders
GROUP BY customer_id, status;
-- Extra: 'Using index' means the group can be read directly from the index

-- Weekly sales report: revenue, orders, new vs returning customer count
SELECT
  YEARWEEK(o.created_at, 1)                 AS week,
  MIN(DATE(o.created_at))                   AS week_start,
  COUNT(DISTINCT o.order_id)                AS total_orders,
  COUNT(DISTINCT o.customer_id)             AS active_customers,
  ROUND(SUM(o.total), 2)                    AS gross_revenue,
  ROUND(AVG(o.total), 2)                    AS avg_order_value,
  -- Customers whose account is newer than 7 days before this week = new customers
  COUNT(DISTINCT CASE
    WHEN c.created_at >= DATE_SUB(MIN(o.created_at), INTERVAL 7 DAY)
    THEN o.customer_id END)                 AS new_customers,
  -- Returning customers = active - new
  COUNT(DISTINCT o.customer_id) -
    COUNT(DISTINCT CASE
      WHEN c.created_at >= DATE_SUB(MIN(o.created_at), INTERVAL 7 DAY)
      THEN o.customer_id END)              AS returning_customers
FROM orders    AS o
JOIN customers AS c ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
  AND o.created_at >= DATE_SUB(CURDATE(), INTERVAL 12 WEEK)
GROUP BY YEARWEEK(o.created_at, 1)
ORDER BY week;

-- WRONG: cannot use aggregate in WHERE clause
SELECT customer_id, SUM(total) AS revenue
FROM orders
WHERE SUM(total) > 500  -- ERROR: Invalid use of group function
GROUP BY customer_id;

-- CORRECT: filter on aggregated values with HAVING
SELECT customer_id, SUM(total) AS revenue
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 500;

-- HAVING with alias (MySQL allows this as an extension)
SELECT customer_id, SUM(total) AS revenue
FROM orders
GROUP BY customer_id
HAVING revenue > 500;  -- MySQL accepts column alias in HAVING

-- Filter on individual columns in WHERE, group conditions in HAVING
SELECT
  customer_id,
  COUNT(*)          AS order_count,
  SUM(total)        AS revenue
FROM orders
WHERE status = 'delivered'     -- WHERE: filter rows before grouping (faster, uses index)
GROUP BY customer_id
HAVING order_count >= 3        -- HAVING: filter groups after aggregation
  AND revenue > 100;

-- Monthly revenue with running total using a CTE + window function
WITH monthly AS (
  SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    ROUND(SUM(total), 2)              AS revenue
  FROM orders
  WHERE status = 'delivered'
  GROUP BY DATE_FORMAT(created_at, '%Y-%m')
)
SELECT
  month,
  revenue,
  SUM(revenue) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS running_total,
  ROUND(revenue / SUM(revenue) OVER () * 100, 1)              AS pct_of_total
FROM monthly
ORDER BY month;

-- List all product names per category as a CSV string
SELECT
  c.name AS category,
  GROUP_CONCAT(p.name ORDER BY p.name SEPARATOR ', ') AS products
FROM categories c
JOIN products p ON c.id = p.category_id
WHERE p.is_active = 1
GROUP BY c.id, c.name;
-- category: Electronics
-- products: Laptop, Mouse, Phone, Tablet

-- Count + list — useful for debugging duplicate detection
SELECT email, COUNT(*) AS cnt,
  GROUP_CONCAT(id ORDER BY id) AS duplicate_ids
FROM users
GROUP BY email
HAVING cnt > 1;

-- Increase max length (default is 1024 bytes)
SET SESSION group_concat_max_len = 65536;