-- Round to different decimal places
SELECT
    123.456 AS original,
    ROUND(123.456, 2) AS two_decimals,
    ROUND(123.456, 1) AS one_decimal,
    ROUND(123.456, 0) AS nearest_integer,
    ROUND(123.456, -1) AS nearest_ten,
    ROUND(123.456, -2) AS nearest_hundred;

-- Round financial data for reporting
SELECT
    order_id,
    amount,
    ROUND(amount, 2) AS rounded_amount,
    ROUND(amount / 1.08, 2) AS pre_tax_amount
FROM orders;

-- CEIL rounds up, FLOOR rounds down
SELECT
    4.2 AS value,
    CEIL(4.2) AS ceiled,
    FLOOR(4.2) AS floored,
    CEIL(-4.2) AS neg_ceiled,
    FLOOR(-4.2) AS neg_floored;

-- TRUNC removes decimals without rounding
SELECT
    9.8765 AS value,
    TRUNC(9.8765, 2) AS truncated_2,
    TRUNC(9.8765, 0) AS truncated_0,
    TRUNC(9.8765, -1) AS truncated_neg1;

-- ABS removes negative signs
SELECT
    -42 AS negative,
    ABS(-42) AS absolute,
    42 AS positive,
    ABS(42) AS still_positive;

-- Calculate price difference regardless of direction
SELECT
    product_name,
    old_price,
    new_price,
    ABS(new_price - old_price) AS price_change,
    CASE
        WHEN new_price > old_price THEN 'Increase'
        WHEN new_price < old_price THEN 'Decrease'
        ELSE 'No Change'
    END AS direction
FROM products;

-- Determine the sign of values
SELECT
    value,
    SIGN(value) AS sign_indicator
FROM (VALUES (-5), (-1), (0), (1), (5)) AS t(value);

-- Calculate powers and roots
SELECT
    2 AS base,
    POWER(2, 3) AS cubed,
    POWER(2, 10) AS power_10,
    SQRT(144) AS square_root,
    POWER(144, 0.5) AS root_via_power;

-- Compound interest formula: A = P(1 + r/n)^(nt)
SELECT
    1000 AS principal,
    0.05 AS annual_rate,
    12 AS compounds_per_year,
    5 AS years,
    ROUND(
        1000 * POWER(1 + 0.05 / 12, 12 * 5), 2
    ) AS future_value;

-- Logarithmic calculations
SELECT
    100 AS value,
    LOG(100) AS log_base10,
    LN(100) AS natural_log,
    LOG(100) / LOG(2) AS log_base2,
    EXP(1) AS euler_number;

-- Use log scale for exponential data visualization
SELECT
    product_name,
    sales_count,
    LOG(sales_count + 1) AS log_sales
FROM product_sales
ORDER BY sales_count DESC;

-- Find even and odd order IDs
SELECT
    order_id,
    CASE
        WHEN order_id % 2 = 0 THEN 'Even'
        ELSE 'Odd'
    END AS parity
FROM orders;

-- Group customers into 10 buckets using modulo
SELECT
    customer_id,
    customer_name,
    MOD(customer_id, 10) AS bucket
FROM customers;

-- PostgreSQL: integer division
SELECT
    div(17, 3) AS quotient,
    mod(17, 3) AS remainder;

-- Basic numeric aggregation
SELECT
    department,
    COUNT(*) AS employee_count,
    SUM(salary) AS total_salary,
    ROUND(AVG(salary), 2) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department;

-- Statistical analysis of salaries
SELECT
    department,
    COUNT(*) AS count,
    ROUND(AVG(salary), 2) AS mean,
    ROUND(VARIANCE(salary), 2) AS variance,
    ROUND(STDDEV(salary), 2) AS std_dev,
    ROUND(STDDEV(salary) / AVG(salary) * 100, 2) AS coeff_of_variation
FROM employees
GROUP BY department;

-- Calculate tiered discounts with precision
SELECT
    product_name,
    price,
    CASE
        WHEN price >= 100 THEN ROUND(price * 0.20, 2)
        WHEN price >= 50 THEN ROUND(price * 0.15, 2)
        ELSE ROUND(price * 0.10, 2)
    END AS discount,
    CASE
        WHEN price >= 100 THEN ROUND(price * 0.80, 2)
        WHEN price >= 50 THEN ROUND(price * 0.85, 2)
        ELSE ROUND(price * 0.90, 2)
    END AS final_price
FROM products;

-- Calculate each department's share of total company salary
SELECT
    department,
    SUM(salary) AS dept_salary,
    ROUND(
        SUM(salary) * 100.0 / SUM(SUM(salary)) OVER(), 1
    ) AS pct_of_total
FROM employees
GROUP BY department
ORDER BY pct_of_total DESC;

-- Calculate month-over-month percentage growth
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
        1
    ) AS growth_pct
FROM monthly;