COUNT(*) | COUNT(column) | COUNT(DISTINCT column)
SUM(column)
AVG(column)
MAX(column)
MIN(column)

SELECT 
    COUNT(*) as total_orders,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(total) as total_revenue,
    AVG(total) as average_order_value,
    MAX(total) as largest_order,
    MIN(total) as smallest_order,
    MAX(order_date) as most_recent_order,
    MIN(order_date) as first_order
FROM

SELECT 
    department,
    COUNT(*) as employee_count,
    SUM(salary) as total_payroll,
    AVG(salary) as avg_salary,
    MAX(salary) as highest_salary,
    MIN(salary) as lowest_salary,
    MAX(salary) - MIN(salary) as salary_range
FROM employees
GROUP BY department
ORDER BY total_payroll DESC

SELECT 
    COUNT(*) as all_rows,                    -- Counts all rows including NULLs
    COUNT(email) as rows_with_email,         -- Counts non-NULL emails
    COUNT(DISTINCT email) as unique_emails,  -- Counts unique emails
    COUNT(phone) as rows_with_phone,
    COUNT(*) - COUNT(email) as missing_emails,
    COUNT(*) - COUNT(phone) as missing_phones
FROM

SELECT 
    product_category,
    COUNT(*) as total_products,
    COUNT(CASE WHEN in_stock = TRUE THEN 1 END) as in_stock_count,
    COUNT(CASE WHEN in_stock = FALSE THEN 1 END) as out_of_stock_count,
    SUM(CASE WHEN price > 100 THEN 1 ELSE 0 END) as premium_products,
    AVG(CASE WHEN in_stock = TRUE THEN price END) as avg_price_in_stock,
    MAX(CASE WHEN in_stock = TRUE THEN price END) as max_available_price
FROM products
GROUP BY

SELECT 
    DATE_TRUNC('MONTH', sale_date) as month,
    COUNT(*) as transaction_count,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(amount) as monthly_revenue,
    AVG(amount) as avg_transaction,
    MAX(amount) as largest_sale,
    MIN(amount) as smallest_sale,
    SUM(amount) / COUNT(DISTINCT customer_id) as revenue_per_customer
FROM sales
GROUP BY DATE_TRUNC('MONTH', sale_date)
ORDER BY month

-- Average of department averages
WITH dept_stats AS (
    SELECT 
        department,
        AVG(salary) as dept_avg_salary,
        COUNT(*) as dept_employee_count
    FROM employees
    GROUP BY department
)
SELECT 
    AVG(dept_avg_salary) as company_avg_of_dept_avgs,
    MAX(dept_avg_salary) as highest_dept_avg,
    MIN(dept_avg_salary) as lowest_dept_avg,
    SUM(dept_employee_count) as total_employees,
    COUNT(*) as number_of_departments
FROM

SELECT 
    COUNT(*) as total_rows,          -- 5
    COUNT(bonus) as non_null_bonus,  -- 3 (ignores 2 NULLs)
    SUM(bonus) as total_bonus,       -- 15000 (ignores NULLs)
    AVG(bonus) as avg_bonus,         -- 5000 (15000/3, not 15000/5)
    SUM(bonus) / COUNT(*) as avg_including_nulls  -- 3000 (15000/5)
FROM

SELECT 
    -- Basic stats
    COUNT(*) as n,
    AVG(price) as mean,
    -- Variance calculation
    AVG(price * price) - (AVG(price) * AVG(price)) as variance,
    -- Standard deviation (approximate)
    SQRT(AVG(price * price) - (AVG(price) * AVG(price))) as std_dev,
    -- Coefficient of variation
    SQRT(AVG(price * price) - (AVG(price) * AVG(price))) / AVG(price) as cv,
    -- Range
    MAX(price) - MIN(price) as range
FROM

SELECT 
    employee_name,
    department,
    salary,
    -- Department aggregates as window functions
    AVG(salary) OVER (PARTITION BY department) as dept_avg,
    MAX(salary) OVER (PARTITION BY department) as dept_max,
    MIN(salary) OVER (PARTITION BY department) as dept_min,
    COUNT(*) OVER (PARTITION BY department) as dept_size,
    -- Comparison to department stats
    salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg,
    salary / AVG(salary) OVER (PARTITION BY department) as ratio_to_avg
FROM employees
ORDER BY department, salary DESC