SQL CTEs and Advanced Analytics: Writing Readable, Powerful Queries:-

(Series: SQL Learning Journey — From Zero to Job-Ready Projects | Part 3)

Introduction

In the first two parts of this series, we explored basic querying and aggregations. Now it’s time to step into advanced SQL techniques used in real data analyst and interview scenarios.

In this article, you’ll learn:

• How to use CTEs (Common Table Expressions)
• How to structure complex logic cleanly
• How to calculate contributions and percentages
• How to solve real interview-style SQL problems

By the end, your SQL code will look professional, readable, and job-ready.

LEVEL 4 — Common Table Expressions (CTEs)

A CTE lets you create temporary result sets using WITH, making queries more readable and modular.

1. Simple CTE for Product Sales

WITH product_sales AS (
    SELECT product,
           SUM(boxes_shipped) AS total_boxes
    FROM [db1].[dbo].[Copy of sample-data-10mins]
    GROUP BY product
)
SELECT *
FROM product_sales;

Instead of repeating subqueries, we store logic once and reuse it cleanly.

2. Using Multiple CTEs to Simulate Join Logic

WITH sales_by_person AS (
    SELECT sales_person,
           product,
           SUM(boxes_shipped) AS sales_boxes
    FROM [db1].[dbo].[Copy of sample-data-10mins]
    GROUP BY sales_person, product
),
product_total AS (
    SELECT product,
           SUM(boxes_shipped) AS total_boxes
    FROM [db1].[dbo].[Copy of sample-data-10mins]
    GROUP BY product
)
SELECT s.sales_person,
       s.product,
       s.sales_boxes,
       p.total_boxes
FROM sales_by_person s
JOIN product_total p
ON s.product = p.product;

This structure is commonly used in business reporting queries.

3. Sales Contribution Per Country

Now we calculate each salesperson’s contribution within their country.

WITH t1 AS (
   SELECT Sales_Person, Country,
          SUM(Amount) AS sales
   FROM [db1].[dbo].[Copy of sample-data-10mins]
   GROUP BY Sales_Person, Country
),
t2 AS (
   SELECT Country,
          SUM(Amount) AS country_total
   FROM [db1].[dbo].[Copy of sample-data-10mins]
   GROUP BY Country
)
SELECT t1.*, 
       t2.country_total,
       CAST(t1.sales*100.0/t2.country_total AS DECIMAL(10,2)) AS contribution
FROM t1
JOIN t2
ON t1.Country = t2.Country;

4. Adding Percentage Symbol to Final Output

SELECT t1.*, 
       t2.country_total,
       CAST(
           CAST(t1.sales*100.0/t2.country_total AS DECIMAL(10,2))
           AS VARCHAR(10)
       ) + '%' AS contribution
FROM t1
JOIN t2
ON t1.Country = t2.Country
ORDER BY contribution DESC;

This is exactly how dashboard percentage values are prepared in SQL.

5. Product Contribution Within Each Country

WITH t1 AS (
   SELECT product, country,
          SUM(boxes_shipped) AS total
   FROM [db1].[dbo].[Copy of sample-data-10mins]
   GROUP BY product, country
),
t2 AS (
   SELECT country,
          SUM(boxes_shipped) AS total_sales
   FROM [db1].[dbo].[Copy of sample-data-10mins]
   GROUP BY country
)
SELECT t1.*, t2.total_sales,
       CAST(
           CAST(t1.total*100.0/t2.total_sales AS DECIMAL(10,2))
           AS VARCHAR(10)
       ) + '%' AS contribution
FROM t1
Inner JOIN t2
ON t1.country = t2.country;

LEVEL 5 — Formatting & Output Control

Professional reports need formatted output.

6. Rounding Aggregated Values

SELECT product,
       CAST(SUM(boxes_shipped) AS DECIMAL(10,2)) AS total_boxes
FROM [db1].[dbo].[Copy of sample-data-10mins]
GROUP BY product;

7. Final Percentage Contribution Formatting

SELECT product,
CAST(
   CAST(
       SUM(boxes_shipped)*100.0 /
       SUM(SUM(boxes_shipped)) OVER ()
       AS DECIMAL(10,2)
   )
AS VARCHAR(10)) + '%' AS contribution
FROM [db1].[dbo].[Copy of sample-data-10mins]
GROUP BY product;

LEVEL 6 — Interview-Style SQL Problems

These queries appear frequently in data analyst interviews.

8. Top 3 Products by Boxes Shipped

SELECT TOP 3 Country, Product,
       SUM(boxes_shipped) AS total_shipped
FROM [db1].[dbo].[Copy of sample-data-10mins]
GROUP BY Country, Product
ORDER BY total_shipped DESC;

9. Top Salesperson per Product

WITH t1 AS (
 SELECT sales_person, product,
        SUM(boxes_shipped) AS total_sales,
        ROW_NUMBER() OVER (
            PARTITION BY product
            ORDER BY SUM(boxes_shipped) DESC
        ) AS rn
 FROM [db1].[dbo].[Copy of sample-data-10mins]
 GROUP BY sales_person, product
)
SELECT *
FROM t1
WHERE rn = 1;

10. Salesperson Contributing More Than 40% of Product Sales

WITH t1 AS (
 SELECT Sales_Person, Product,
        SUM(amount) AS product_sales
 FROM [db1].[dbo].[Copy of sample-data-10mins]
 GROUP BY Sales_Person, Product
),
t2 AS (
 SELECT Product,
        SUM(amount) AS total_sales
 FROM [db1].[dbo].[Copy of sample-data-10mins]
 GROUP BY Product
),
t3 AS (
 SELECT t1.*, t2.total_sales,
        CAST(t1.product_sales*100.0/t2.total_sales AS DECIMAL(10,2)) AS contri
 FROM t1
 JOIN t2 ON t1.Product = t2.Product
)
SELECT *
FROM t3
WHERE contri > 40;

11. Products with Total Boxes > 1000

SELECT Product,
       SUM(boxes_shipped) AS total_boxes
FROM [db1].[dbo].[Copy of sample-data-10mins]
GROUP BY Product
HAVING SUM(boxes_shipped) > 1000;

12. Products Contributing More Than 20% of Total Sales

WITH t1 AS (
 SELECT Product,
        SUM(boxes_shipped) AS total_boxes,
        CAST(
           SUM(boxes_shipped)*100.0 /
           SUM(SUM(boxes_shipped)) OVER ()
           AS DECIMAL(10,2)
        ) AS percent
 FROM [db1].[dbo].[Copy of sample-data-10mins]
 GROUP BY Product
)
SELECT *
FROM t1
WHERE percent > 20;

Conclusion

In this part, you learned how to:

• Write clean modular SQL using CTEs
• Perform advanced percentage and contribution analysis
• Format professional outputs
• Solve real interview-style SQL problems

These are the exact techniques used in real-world reporting systems and data analyst interviews.

Final Insight

Good SQL isn’t just about getting results — it’s about writing queries others can understand and trust.