-- COUNT(*): total rows in the table
SELECT COUNT(*) AS total_orders FROM orders;

-- COUNT(column): counts only non-NULL values
SELECT COUNT(discount_code) AS orders_with_discount FROM orders;
-- If discount_code is NULL for 70% of rows, this returns 30% of COUNT(*)

-- COUNT(DISTINCT): unique values
SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM orders;

-- Comparison in one query
SELECT
  COUNT(*)                        AS total_orders,
  COUNT(discount_code)            AS discounted_orders,
  COUNT(DISTINCT customer_id)     AS unique_buyers,
  COUNT(*) - COUNT(discount_code) AS full_price_orders
FROM orders
WHERE status = 'delivered';

-- Total revenue from delivered orders
SELECT ROUND(SUM(total), 2) AS total_revenue
FROM orders
WHERE status = 'delivered';

-- SUM with expression
SELECT
  SUM(quantity * unit_price)                 AS gross_revenue,
  SUM(quantity * unit_price * discount_rate) AS total_discount,
  SUM(quantity * unit_price * (1 - discount_rate)) AS net_revenue
FROM order_items;

-- SUM ignores NULL values — safe if some totals are NULL
SELECT SUM(bonus_amount) AS total_bonuses FROM employees;
-- Employees with NULL bonus_amount are excluded from the sum

-- Average order value
SELECT ROUND(AVG(total), 2) AS avg_order_value FROM orders;

-- AVG ignores NULLs — this may not be what you want
SELECT AVG(discount_amount) AS avg_discount FROM orders;
-- Only averages rows where discount_amount IS NOT NULL
-- Rows with no discount are excluded, inflating the average

-- To include zeros for NULL discounts:
SELECT AVG(COALESCE(discount_amount, 0)) AS avg_discount FROM orders;

-- Price range of products
SELECT
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive,
  MAX(price) - MIN(price) AS price_spread
FROM products
WHERE is_active = 1;

-- MIN/MAX on dates
SELECT
  MIN(created_at) AS first_order,
  MAX(created_at) AS most_recent_order,
  DATEDIFF(MAX(created_at), MIN(created_at)) AS days_active
FROM orders
WHERE customer_id = 42;

-- MIN/MAX on strings (alphabetical comparison using column collation)
SELECT MIN(last_name) AS first_alpha, MAX(last_name) AS last_alpha
FROM employees;

-- NULL handling demo
CREATE TEMPORARY TABLE scores (
  student VARCHAR(20),
  score   INT
);
INSERT INTO scores VALUES
  ('Alice', 90), ('Bob', 80), ('Carol', NULL), ('Dave', 70);

SELECT
  COUNT(*)      AS rows,           -- 4 (includes Carol's NULL row)
  COUNT(score)  AS non_null,       -- 3 (excludes Carol)
  SUM(score)    AS total,          -- 240 (NULL treated as 0 in sum)
  AVG(score)    AS average,        -- 80.0 (240 / 3, not 240 / 4)
  MIN(score)    AS minimum,        -- 70
  MAX(score)    AS maximum         -- 90
FROM scores;

-- SUM of distinct values (unusual but valid)
SELECT SUM(DISTINCT price) FROM products;
-- Adds each unique price only once

-- AVG of distinct values
SELECT AVG(DISTINCT salary) FROM employees;

-- Most commonly used: COUNT(DISTINCT col)
SELECT
  COUNT(DISTINCT product_id)  AS unique_products_sold,
  COUNT(DISTINCT customer_id) AS unique_buyers,
  COUNT(DISTINCT order_id)    AS total_orders
FROM order_items
JOIN orders USING (order_id)
WHERE orders.created_at >= '2024-01-01';

-- List all product names per category
SELECT
  cat.name AS category,
  GROUP_CONCAT(p.name ORDER BY p.name SEPARATOR ', ') AS products
FROM categories AS cat
JOIN products   AS p ON cat.category_id = p.category_id
WHERE p.is_active = 1
GROUP BY cat.category_id, cat.name;

-- List order IDs per customer (limited length)
SELECT
  customer_id,
  GROUP_CONCAT(order_id ORDER BY created_at SEPARATOR ',') AS order_ids,
  COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 10;

-- GROUP_CONCAT has a default max length of 1024 bytes
-- Increase it for long strings
SET SESSION group_concat_max_len = 100000;

-- Population standard deviation and variance
SELECT
  AVG(total)          AS mean_order_value,
  STDDEV_POP(total)   AS population_std,
  VARIANCE(total)     AS population_variance,
  STDDEV_SAMP(total)  AS sample_std,            -- use for samples
  VAR_SAMP(total)     AS sample_variance
FROM orders
WHERE status = 'delivered';

-- Use std dev to find unusually large orders (outliers)
SELECT order_id, customer_id, total
FROM orders, (
  SELECT AVG(total) AS avg_val, STDDEV_POP(total) AS std_val
  FROM orders WHERE status = 'delivered'
) AS stats
WHERE total > avg_val + 2 * std_val
  AND status = 'delivered'
ORDER BY total DESC;

-- BIT_AND: all permissions a user has in ALL roles (intersection)
-- BIT_OR:  all permissions a user has in ANY role (union)
SELECT
  user_id,
  BIT_AND(permission_mask) AS permissions_in_all_roles,
  BIT_OR(permission_mask)  AS permissions_in_any_role
FROM user_roles
GROUP BY user_id;

-- Summary statistics for the entire orders table
SELECT
  COUNT(*)                                AS total_orders,
  COUNT(DISTINCT customer_id)             AS unique_customers,
  ROUND(SUM(total), 2)                    AS gross_revenue,
  ROUND(AVG(total), 2)                    AS avg_order_value,
  ROUND(MIN(total), 2)                    AS smallest_order,
  ROUND(MAX(total), 2)                    AS largest_order,
  COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders
WHERE created_at >= '2024-01-01';

-- Pivot-style report: order counts by status in one row
SELECT
  COUNT(*) AS total,
  COUNT(CASE WHEN status = 'pending'    THEN 1 END) AS pending,
  COUNT(CASE WHEN status = 'shipped'    THEN 1 END) AS shipped,
  COUNT(CASE WHEN status = 'delivered'  THEN 1 END) AS delivered,
  COUNT(CASE WHEN status = 'cancelled'  THEN 1 END) AS cancelled,
  ROUND(SUM(CASE WHEN status = 'delivered' THEN total ELSE 0 END), 2) AS delivered_revenue
FROM orders
WHERE created_at >= '2024-01-01';

-- Monthly sales report with all key metrics
SELECT
  DATE_FORMAT(o.created_at, '%Y-%m')          AS month,
  COUNT(DISTINCT o.order_id)                  AS total_orders,
  COUNT(DISTINCT o.customer_id)               AS unique_customers,
  SUM(oi.quantity)                            AS units_sold,
  ROUND(SUM(oi.quantity * oi.unit_price), 2)  AS gross_revenue,
  ROUND(AVG(o.total), 2)                      AS avg_order_value,
  ROUND(MAX(o.total), 2)                      AS max_order,
  ROUND(STDDEV_SAMP(o.total), 2)              AS revenue_std_dev
FROM orders      AS o
JOIN order_items AS oi ON o.order_id = oi.order_id
WHERE o.status = 'delivered'
  AND o.created_at >= '2024-01-01'
GROUP BY month
ORDER BY month;