📊 Finance Analytics SQL Project

Description

Croma India and AtliQ Business leaders want to understand their product-wise performance, top markets, and key customers for the fiscal year 2021. As part of the data analytics team, you will help them generate actionable insights by creating structured SQL solutions. The reports will be consumed by product owners and executives to drive future business decisions.

---

Problem Statement

The management team requires automated reports to track sales performance at different levels. The following key tasks have been assigned:

A. Croma India Product-Wise Sales Report

As a product owner, I want to generate a report of individual product sales (aggregated on a monthly basis at the product code level) for FY-2021 so that I can track product performance and run further analytics in Excel.

Required fields:

• Month
• Product Name
• Variant
• Sold Quantity
• Gross Price Per Item
• Gross Price Total

B. Net Sales Report for Top Markets, Products, and Customers

As a business executive, I want to generate a consolidated Net Sales report for FY-2021 to identify top-performing regions, products, and customers.

Required reports:

---

Task

• Imagine yourself as a SQL Data Analyst and perform the following tasks:
• Create SQL queries, views, and stored procedures to generate the required reports.
• Optimize the queries using techniques like generated columns, indexing, and top-N filtering.
• Document the steps clearly so that business users and technical reviewers can follow your logic.
• Optionally, export the outputs in a format suitable for Excel/PowerBI to support further analysis.

---

Tools & Skills Showcased

• SQL (DDL, DML, Stored Procedures, Views, Window Functions, Optimization)
• Data Modeling
• Business Reporting (Finance & Sales Analytics)

---

Solution :

A. Croma India Product-Wise Sales Report

Step 1: Customer codes for Croma India

SELECT *	
FROM dim_customer	
WHERE customer like "%croma%"	
and market = "india" ;

🔎 Insights :

• Customer Code used: 90002002 (represents Croma India)
• All subsequent queries and reports are based on this customer.

Step 2: Map Raw Dates to Fiscal Year with Stored Function -> get_fiscal_year()

Why this step matters:

• Croma’s financial year starts September 1st, not January 1st.
• Raw calendar dates need to be converted into fiscal years for accurate reporting and aggregation.
• This ensures that monthly and yearly summaries align with business financial reporting.

Approach:

• Shift the calendar date by 4 months to align with fiscal year logic.
• Create a reusable SQL function get_fiscal_year(date) so the logic doesn’t need to be repeated in every query.
• Use this function in queries to filter sales data for a particular fiscal year.

➡️ create Function

CREATE FUNCTION `get_fiscal_year` (
calender_year date
)
RETURNS INTEGER
deterministic
BEGIN
declare fiscal_year INT;
set fiscal_year = year(date_add(date, interval 4 month));
RETURN fiscal_year;
END

➡️ Procedure Call:

• select gdb0041.get_fiscal_year('2021-12-01')

Outcome:

• All monthly sales transactions for Croma India now have a fiscal year assigned.

Step 3: Extract Monthly Sales Data for fiscal year 2021

SELECT *
FROM fact_sales_monthly
	WHERE customer_code = 90002002 and 
	get_fiscal_year(date) = 2021
ORDER BY date DESC
LIMIT 100000;

📝 Sample Output Table

🔎 Insights :

• Queried the monthly sales fact table for customer_code = 90002002
• This provides raw monthly sales transactions

Step 4: Retrieve Product Name and Variant

Why this step matters:

• Raw sales data contains only product_code. To make reports meaningful for business stakeholders, we need human-readable product names and variants.

Approach:

• Join fact_sales_monthly with dim_product on product_code.
• Filter for Croma India (customer_code = 90002002) and FY 2021 using the get_fiscal_year() function.
• Order results by date to get a chronological view of sales.

SELECT
	s.date,
	p.product_code,p.product,p.variant,
	s.sold_quantity
FROM fact_sales_monthly s
JOIN dim_product p
	ON s.product_code = p.product_code
WHERE customer_code = 90002002 and
      get_fiscal_year(date) = 2021
ORDER BY date DESC
LIMIT 100000;

📝 Sample Output Table

🔎 Insights :

• Linking product_code with product name and variant improves clarity and supports all downstream sales analyses.

Step 5: Calculate Gross Price Per Item and Gross Price Total

Why this step matters:

• The earlier steps only gave us quantities sold.
• To make the report business-relevant, we must also include financial metrics.
• Gross Price Per Item and Gross Price Total are key measures for revenue analysis and decision-making.

Approach:

• Join fact_sales_monthly with fact_gross_price using product_code and fiscal_year.
• Use ROUND() for readability (2 decimal places).
• Add calculated column:

gross_price_per_item = gross price of one unit.

gross_price_total = gross price per item × sold quantity.

SELECT
	s.date,
	p.product_code,p.product,p.variant,
	s.sold_quantity,
	ROUND(g.gross_price,2) as gross_price_per_item,
	ROUND(gross_price * sold_quantity,2) as gross_price_total

FROM fact_sales_monthly s
JOIN dim_product p
	ON s.product_code = p.product_code
JOIN fact_gross_price g
	ON p.product_code = g.product_code and
	g.fiscal_year = get_fiscal_year(s.date)

WHERE customer_code = 90002002 and
 	  get_fiscal_year(date) = 2021
ORDER BY date DESC
LIMIT 1000000;

🔎 Insights :

• This step establishes the baseline revenue (Gross Sales) before applying any discounts or deductions.

---

B. Net Sales Report for Top Markets, Products, and Customers

1. Report for Top Markets

Step 1: Apply Pre-Invoice Deductions

Why this matters

• Gross Price alone doesn’t reflect the real revenue.
• Customers (like Croma, Amazon, etc.) often receive discounts, rebates, or promotional deductions.
• To calculate Net Sales, we must adjust Gross Sales by subtracting pre-invoice deductions.

Approach

• Join fact_sales_monthly with fact_pre_invoice_deductions using customer_code and fiscal_year.
• Apply pre_invoice_discount_pct to adjust Net Sales.

SELECT
	s.date,
	s.product_code,
	p.product, p.variant,
	s.sold_quantity,
	g.gross_price as gross_price_per_item,
	ROUND(g.gross_price * s.sold_quantity,2) as gross_price_total,
	pre.pre_invoice_discount_pct

FROM fact_sales_monthly s
JOIN dim_product p
	ON p.product_code = s.product_code
JOIN fact_gross_price g
	ON g.product_code = s.product_code
	and g.fiscal_year = get_fiscal_year(s.date)
JOIN fact_pre_invoice_deductions pre
	ON pre.customer_code = s.customer_code
	and pre.fiscal_year = get_fiscal_year(s.date)

WHERE
get_fiscal_year(date) = 2021
LIMIT 1000000
;

🔥Optimization with Generated Column : fiscal_year

• Instead of calling get_fiscal_year(date) repeatedly (performance hit), create a generated column fiscal_year inside fact_sales_monthly.

updated query

 SELECT
     s.date,
     s.fiscal_year, 
     s.product_code, 
     p.product, 
     p.variant,
     s.sold_quantity,
     g.gross_price AS gross_price_per_item,
     ROUND(g.gross_price * s.sold_quantity,2) as gross_price_total,
     pre.pre_invoice_discount_pct
FROM fact_sales_monthly s
JOIN dim_product p
    ON p.product_code = s.product_code
JOIN fact_gross_price g
    ON g.product_code = s.product_code
   AND g.fiscal_year = s.fiscal_year  
JOIN fact_pre_invoice_deductions pre
    ON pre.customer_code = s.customer_code
   AND pre.fiscal_year = s.fiscal_year  
WHERE s.fiscal_year = 2021  
LIMIT 1000000;

📝 Sample Output Table

✅ Result

Normal query vs Optimized query fetch time

🔎 Insights

• By applying pre-invoice deductions and optimizing with a generated fiscal_year column, we ensure:
• Faster query execution by 85 %.

Step 2 : Net Invoice Sales with Common Table Expression (CTE) and Views

a) Net Invoice Sales with CTE

Why does it matter

• Net Invoice Sales shows the revenue after pre-invoice deductions, giving a true picture of sales before any post-invoice adjustments.
• A CTE works like a temporary derived table.
• CTE simplifies complex queries by breaking them into readable, reusable steps, making Net Sales calculations easier to manage.

🧩 Why the need to use CTE in the query ?

❌ We cannot directly write:

  gross_price_total - pre_invoice_discount_pct as net_invoice_sales

because gross_price_total is itself a derived column:

ROUND(s.sold_quantity * g.gross_price, 2) as gross_price_total

Approach

• Join fact_sales_monthly with fact_gross_price using product_code and fiscal_year.
• Bring in fact_pre_invoice_deductions to capture discount percentages.
• Use a CTE to organize the intermediate results before calculating Net Invoice Sales.

WITH CTE AS (
    SELECT 
        s.date,
		s.fiscal_year,  
        s.customer_code,
        s.product_code, 
        p.product, p.variant, 
        s.sold_quantity, 
        g.gross_price AS gross_price_per_item,
        ROUND(s.sold_quantity * g.gross_price, 2) as gross_price_total,
        pre.pre_invoice_discount_pct
    FROM fact_sales_monthly s
    JOIN dim_product p
        ON s.product_code = p.product_code
    JOIN fact_gross_price g
        ON g.fiscal_year = s.fiscal_year
        AND g.product_code = s.product_code
    JOIN fact_pre_invoice_deductions as pre
        ON pre.customer_code = s.customer_code
        AND pre.fiscal_year = s.fiscal_year
    WHERE s.fiscal_year = 2021
)
SELECT
    *,
    (gross_price_total - pre_invoice_discount_pct * gross_price_total) as net_invoice_sales
FROM CTE
LIMIT 1000000;

b) Net Invoice Sales with Views

Why does it matter

• Views simplifies queries.
• CTEs are temporary, whereas views are persistent and can provide user access control.

➡️ create Views

CREATE  VIEW `sales_preinv_discount` AS
SELECT
        s.date,
		s.fiscal_year, 
        s.customer_code,
        c.market,
        s.product_code, 
        p.product, p.variant, 
        s.sold_quantity, 
        g.gross_price as gross_price_per_item,
        ROUND(s.sold_quantity * g.gross_price, 2) as gross_price_total,
        pre.pre_invoice_discount_pct
FROM fact_sales_monthly s
JOIN dim_customer c 
		ON c.customer_code = s.customer_code
JOIN dim_product p
        ON s.product_code = p.product_code
JOIN fact_gross_price g
        ON g.fiscal_year = s.fiscal_year
        and g.product_code = s.product_code
JOIN fact_pre_invoice_deductions as pre
        ON pre.customer_code = s.customer_code
        and pre.fiscal_year = s.fiscal_year

Hence, net_invoice_sales

SELECT
    *,
    (gross_price_total - pre_invoice_discount_pct * gross_price_total) as net_invoice_sales
FROM sales_preinv_discount
LIMIT 1000000;

Sample Output Table

🔎 Insight :

• Using a CTE clearly calculates Net Invoice Sales, providing a more accurate view of revenue after discounts—ideal for analysis or visualization.

Step 3 : Post Invoice Deductions

Why does it matter

• Post-invoice deductions (like discounts, rebates, or other deductions applied after billing) further reduce actual revenue and must be considered for accurate Net Sales.

Approach

• Create a view sales_postinv_discount to combine sales, product details, gross price, pre-invoice deductions, and post-invoice deductions.
• Join sales_preinv_discount with fact_post_invoice_deductions using customer_code,product_code and date.
• Calculate total discount, post_invoice_discount_pct by adding discounts_pct and other_deductions_pct.

➡️ create Views

CREATE VIEW `sales_postinv_discount` AS 
SELECT
		s.date, 
		s.fiscal_year,
		s.customer_code,
		s.market,
	    s.product_code, 
		s.product, 
		s.variant, 
		s.sold_quantity, 
		s.gross_price_total,
        s.pre_invoice_discount_pct,
		(gross_price_total - pre_invoice_discount_pct * gross_price_total) as net_invoice_sales,
        (po.discounts_pct + po.other_deductions_pct) as post_invoice_discount_pct 
FROM sales_preinv_discount s
JOIN fact_post_invoice_deductions po
		ON s.customer_code = po.customer_code
    	and s.product_code = po.product_code
        and s.date = po.date;

🔎 Insights :

• By creating the sales_postinv_discount view:
  • You reduce repetitive code.
  • You make Net Sales accessible to any analyst without them needing to understand the full ETL logic.

Step 4 : Net Sales

Why does it matter

• Net Sales is the true revenue after all pre- and post-invoice deductions.
• Provides the final number used in performance analysis, reporting, and decision-making.

Approach

• Use the sales_postinv_discount view as input.
• Create a new view net_sales that applies the final adjustment: (net_invoice_sales - net_invoice_sales * post_invoice_discount_pct).
• This consolidates all logic (gross → pre-invoice → post-invoice → net sales) into one view.

➡️ create Views

CREATE VIEW `net_sales` AS
SELECT *,
ROUND(net_invoice_sales - net_invoice_sales * post_invoice_discount_pct,2) as net_sales  
FROM sales_postinv_discount;

📝 Sample Output Table

🔎 Insights :

• A single net_sales view now delivers the final, ready-to-use metric.
• Reporting for top markets, products, or customers becomes just a simple aggregation query.

Step 5: Top N Markets by Net Sales with Stored Procedure

Why this matters:

• Automates market-level insights without rewriting queries.
• Enables quick comparisons of regional performance for any fiscal year or limit (Top 5, Top 10, etc.).
• Ideal for executive dashboards and regional sales analysis.

Approach:

• Stored proc to get top n markets by net sales for a given year

➡️ create Stored procedure

CREATE PROCEDURE `get_top_n_markets_by_net_sales` (
	in_fiscal_year INT,
	in_top_n INT
)
BEGIN
SELECT
market,
ROUND(SUM(net_sales)/1000000,2) as net_sales_mln
FROM net_sales
WHERE fiscal_year = in_fiscal_year
GROUP BY market
ORDER BY net_sales DESC
LIMIT in_top_n;
END

Procedure Call :

call gdb0041.get_top_n_markets_by_net_sales(2021, 3);

🔎 Insights :

• The USA emerges as the largest market, contributing the highest share of net sales in 2021.
• India and Germany follow closely, highlighting both mature and growing markets.
• These Top 3 together account for a majority of the company’s net sales, signaling where strategic attention should remain.

Step 6: Top N Customers by Net Sales with Stored Procedure

a) Top N customers with normal query

Why does it matter

• Identifies which customers contribute most to revenue in a given fiscal year.
• Enables sales teams to prioritize key accounts.

Approach:

• Join net_sales with dim_customer using customer_code.
• Group by customer, sum up net_sales, convert to millions.
• Order by descending sales and limit to top N.

SELECT
c.customer,
ROUND(SUM(net_sales)/1000000,2) as net_sales_mln
FROM net_sales s
JOIN dim_customer c
	ON s.customer_code = c.customer_code
WHERE fiscal_year = 2021
GROUP BY c.customer
ORDER BY net_sales_mln DESC
LIMIT 2;

🔎 Insight :

• Amazon and Atliq Exclusive are the top contributors overall, together forming a significant portion of total sales revenue in FY 2021.

b) Top N customers with stored procedure

Why this matters:

• A stored procedure allows dynamic querying — no need to manually edit queries each time you change the market or number of customers.
• Great for automation, dashboards, and reporting systems where parameters vary.

Approach:

• Create a stored procedure get_top_n_customers_by_net_sales.
• Accept market, fiscal_year, and top N as input parameters.
• Filter by market and year, group by customer, and return the top N results.

➡️ create Stored procedure

CREATE PROCEDURE `get_top_n_customers_by_net_sales`(
	in_market VARCHAR(45),
    in_fiscal_year INT,
	in_top_n INT
)
BEGIN
SELECT
c.customer, 
round(sum(net_sales)/1000000,2) as net_sales_mln
FROM net_sales s
JOIN dim_customer c
	ON s.customer_code=c.customer_code
    and s.market = in_market
WHERE
	s.fiscal_year= in_fiscal_year
GROUP BY c.customer
ORDER BY net_sales_mln desc
LIMIT in_top_n;
END

Procedure Call:

CALL get_top_n_customers_by_net_sales('India', 2021, 3);

Result :

🔎 Insights :

• Within the India market, Amazon remains the top customer, but its contribution (₹ 30 M) is much smaller than its global total (₹ 109 M).
• This difference arises because the earlier query covered all markets combined, whereas this stored procedure filters by a specific market.
• The procedure offers flexibility to analyze performance region-wise without rewriting SQL every time.

Step 7: Top N Products by Net Sales with Stored Procedure

Why this matters:

• Helps dynamically identify best-performing products across different years.
• Identifies which products generate the highest sales revenue in a given year.
• Helps product and marketing teams focus on high-performing SKUs and forecast demand effectively.

Approach:

• Create a stored procedure top_n_products_by_net_sales.
• Accept fiscal year and top N as input parameters.
• Join net_sales with dim_product to map product names.
• Group, sum, and order results by descending sales.

➡️ create Stored procedure

CREATE PROCEDURE `top_n_products_by_net_sales`(
in_fiscal_year INT,
in_top_n INT
)
BEGIN
SELECT
p.product,
ROUND(SUM(net_sales)/1000000,2) as net_sales_mln
FROM net_sales s
JOIN dim_product p
	ON s.product_code = p.product_code
WHERE fiscal_year = in_fiscal_year
GROUP BY p.product
ORDER BY net_sales_mln DESC
LIMIT in_top_n;
END

Procedure Call:

call gdb0041.top_n_products_by_net_sales(2021, 2);

Result :

🔎 Insights :

• AQ BZ Allin 1 leads the sales chart with ₹33.75M in FY 2021, indicating strong market demand and possibly higher profit margins.
• AQ Qwerty follows closely, showing consistent performance across multiple markets.
• Together, they account for a significant share of total revenue, making them key products to prioritize for production and distribution.