Fini Analytics SQL Queries: Complete Reference Guide

SELECT
    COUNT(*) AS total_sales,
    SUM(revenue) AS total_revenue,
    SUM(margin) AS total_margin,
    AVG(revenue) AS avg_sale_value,
    SUM(units) AS total_units_sold
FROM fact_sales;

SELECT
    c.purchase_frequency,
    COUNT(DISTINCT c.customer_id) AS customers,
    ROUND(SUM(f.revenue)::numeric, 2) AS revenue,
    ROUND(AVG(f.revenue)::numeric, 2) AS avg_sale
FROM fact_sales f
JOIN dim_customers c
    ON f.customer_id = c.customer_id
GROUP BY c.purchase_frequency
ORDER BY revenue DESC;

SELECT
    p.product_name,
    p.category,
    SUM(f.units) AS units_sold,
    ROUND(SUM(f.revenue)::numeric, 2) AS revenue,
    ROUND(SUM(f.margin)::numeric, 2) AS margin
FROM fact_sales f
JOIN dim_products p
    ON f.product_id = p.product_id
GROUP BY
    p.product_name,
    p.category
ORDER BY revenue DESC
LIMIT 10;

SELECT
    EXTRACT(MONTH FROM f.sale_date) AS month,
    p.season,
    SUM(f.units) AS units_sold
FROM fact_sales f
JOIN dim_products p
    ON f.product_id = p.product_id
GROUP BY
    month,
    p.season
ORDER BY
    month,
    p.season;

SELECT
    discount,
    COUNT(*) AS transactions,
    ROUND(AVG(units)::numeric, 2) AS avg_units,
    ROUND(AVG(revenue)::numeric, 2) AS avg_revenue,
    ROUND(AVG(margin)::numeric, 2) AS avg_margin
FROM fact_sales
GROUP BY discount
ORDER BY discount;

SELECT
    c.region,
    ROUND(SUM(f.revenue)::numeric, 2) AS revenue,
    ROUND(SUM(f.margin)::numeric, 2) AS margin,
    SUM(f.units) AS units
FROM fact_sales f
JOIN dim_customers c
    ON f.customer_id = c.customer_id
GROUP BY c.region
ORDER BY revenue DESC;

SELECT
    days_since_launch,
    ROUND(AVG(revenue)::numeric,2) AS avg_revenue
FROM vw_product_launches
WHERE days_since_launch <= 90
GROUP BY days_since_launch
ORDER BY days_since_launch;

SELECT
    product_name,
    ROUND(SUM(revenue)::numeric,2) AS revenue,
    ROUND(SUM(margin)::numeric,2) AS margin,
    SUM(units) AS units
FROM vw_product_launches
WHERE days_since_launch <= 90
GROUP BY product_name
ORDER BY revenue DESC;

SELECT
    category,
    ROUND(SUM(revenue)::numeric,2) AS revenue,
    ROUND(SUM(margin)::numeric,2) AS margin
FROM vw_product_launches
WHERE days_since_launch <= 90
GROUP BY category
ORDER BY revenue DESC;