SQL Analysis — Financial Performance Analysis¶
Setup
Load financials_clean.csv into a table financials. Key columns: segment, country, product, discount_band, units_sold, sale_price, gross_sales, discounts, sales, cogs, profit, year, month_number, year_month.
Q1 — Company P&L Summary¶
SELECT
ROUND(SUM(gross_sales), 0) AS gross_sales,
ROUND(SUM(discounts), 0) AS total_discounts,
ROUND(SUM(sales), 0) AS net_sales,
ROUND(SUM(cogs), 0) AS cogs,
ROUND(SUM(profit), 0) AS gross_profit,
ROUND(SUM(profit) / SUM(sales) * 100, 1) AS gross_margin_pct,
ROUND(SUM(discounts) / SUM(gross_sales) * 100, 1) AS discount_rate_pct
FROM financials;
This is the top-line P&L — exactly what goes at the top of a board report.
Q2 — Profit by Segment (Profit, Not Just Revenue)¶
SELECT
segment,
ROUND(SUM(sales), 0) AS revenue,
ROUND(SUM(profit), 0) AS profit,
ROUND(SUM(profit) / SUM(sales) * 100, 1) AS margin_pct,
ROUND(SUM(sales) / SUM(SUM(sales)) OVER () * 100, 1) AS revenue_share_pct
FROM financials
GROUP BY segment
ORDER BY profit DESC;
Revenue share vs profit share
A segment can have a large revenue share but small profit share if its margin is low. Always compare the two — it reveals which segments truly drive the bottom line.
Q3 — Monthly P&L Trend with Growth¶
WITH monthly AS (
SELECT
year_month,
SUM(sales) AS revenue,
SUM(profit) AS profit
FROM financials
GROUP BY year_month
)
SELECT
year_month,
ROUND(revenue, 0) AS revenue,
ROUND(profit, 0) AS profit,
ROUND(profit / revenue * 100, 1) AS margin_pct,
ROUND(LAG(revenue) OVER (ORDER BY year_month), 0) AS prev_month_revenue,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY year_month))
/ LAG(revenue) OVER (ORDER BY year_month) * 100, 1
) AS mom_growth_pct
FROM monthly
ORDER BY year_month;
Q4 — Product Profitability (Find the Volume Traps)¶
SELECT
product,
ROUND(SUM(units_sold), 0) AS units,
ROUND(SUM(sales), 0) AS revenue,
ROUND(SUM(profit), 0) AS profit,
ROUND(SUM(profit) / SUM(sales) * 100, 1) AS margin_pct
FROM financials
GROUP BY product
ORDER BY revenue DESC;
Look for products with high revenue rank but low margin — these are "volume traps" that look successful but barely contribute to profit.
Q5 — Discount Impact by Band¶
SELECT
discount_band,
COUNT(*) AS transactions,
ROUND(SUM(gross_sales), 0) AS gross_sales,
ROUND(SUM(discounts), 0) AS discounts_given,
ROUND(SUM(sales), 0) AS net_sales,
ROUND(SUM(profit), 0) AS profit,
ROUND(SUM(profit) / SUM(sales) * 100, 1) AS margin_pct
FROM financials
GROUP BY discount_band
ORDER BY
CASE discount_band
WHEN 'None' THEN 1 WHEN 'Low' THEN 2
WHEN 'Medium' THEN 3 WHEN 'High' THEN 4
END;
Watch the margin decline across discount bands
As discount band increases, margin should fall. If High-discount transactions have negative margin, the company is discounting below cost — a pricing governance problem.
Q6 — Country Profitability¶
SELECT
country,
ROUND(SUM(sales), 0) AS revenue,
ROUND(SUM(profit), 0) AS profit,
ROUND(SUM(profit) / SUM(sales) * 100, 1) AS margin_pct
FROM financials
GROUP BY country
ORDER BY profit DESC;
Q7 — Year-over-Year Comparison¶
SELECT
month_name,
ROUND(SUM(CASE WHEN year = 2013 THEN sales END), 0) AS sales_2013,
ROUND(SUM(CASE WHEN year = 2014 THEN sales END), 0) AS sales_2014,
ROUND(
(SUM(CASE WHEN year = 2014 THEN sales END) - SUM(CASE WHEN year = 2013 THEN sales END))
/ SUM(CASE WHEN year = 2013 THEN sales END) * 100, 1
) AS yoy_growth_pct
FROM financials
GROUP BY month_name, month_number
ORDER BY month_number;
Q8 — Budget vs Actual Variance¶
-- Assumes a budget table or budget_variance.csv loaded as `budget`
SELECT
b.year_month,
b.segment,
ROUND(b.actual_sales, 0) AS actual,
ROUND(b.budget_sales, 0) AS budget,
ROUND(b.actual_sales - b.budget_sales, 0) AS variance,
ROUND((b.actual_sales - b.budget_sales) / b.budget_sales * 100, 1) AS variance_pct,
CASE
WHEN b.actual_sales >= b.budget_sales THEN 'Favourable'
ELSE 'Unfavourable'
END AS variance_type
FROM budget b
ORDER BY b.year_month, variance ASC;