Master T-SQL for Data Analytics & Power BI in Just 5 Days!

Tuesday, July 15, 2025

Master T-SQL for Data Analytics & Power BI in Just 5 Days!

✅ 1. Practice Dataset: Sales Scenario

We’ll work with 4 tables – Customers, Products, Orders, and OrderDetails.

-- Customers Table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    Region NVARCHAR(50),
    JoinDate DATE
);

-- Products Table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Category NVARCHAR(50),
    Price DECIMAL(10,2)
);

-- Orders Table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10,2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- OrderDetails Table
CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    LineTotal DECIMAL(10,2),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

✅ Sample Data

-- Customers
INSERT INTO Customers VALUES
(1, 'Amit', 'Sharma', 'amit@xyz.com', 'North', '2022-05-01'),
(2, 'Priya', 'Kumar', 'priya@xyz.com', 'South', '2023-01-15'),
(3, 'Ravi', 'Patel', 'ravi@xyz.com', 'East',  '2021-11-20'),
(4, 'Sneha', 'Verma', 'sneha@xyz.com', 'West', '2022-08-10');

-- Products
INSERT INTO Products VALUES
(1, 'Laptop', 'Electronics', 55000),
(2, 'Mouse', 'Electronics', 800),
(3, 'Desk Chair', 'Furniture', 4500),
(4, 'Notebook', 'Stationery', 50);

-- Orders
INSERT INTO Orders VALUES
(101, 1, '2023-03-10', 55800),
(102, 2, '2023-04-02', 5000),
(103, 1, '2023-06-18', 4550),
(104, 4, '2023-07-01', 5050);

-- OrderDetails
INSERT INTO OrderDetails VALUES
(1001, 101, 1, 1, 55000),
(1002, 101, 2, 1, 800),
(1003, 102, 3, 1, 4500),
(1004, 103, 3, 1, 4500),
(1005, 104, 4, 10, 500);

This dataset is small but realistic and works well for analytics queries.

✅ 2. 20 Hands-On Exercises

🗓 Day 1 – SQL Basics & Filtering

1️⃣ Get all customers who joined after 2023-01-01.
2️⃣ List all unique product categories.
3️⃣ Show all orders where TotalAmount > 10,000.
4️⃣ Find customers who have no email address (NULL).

🗓 Day 2 – Joins

5️⃣ Show all orders with customer full names.
6️⃣ List products along with their OrderID & Quantity sold.
7️⃣ Get all customers and their orders, including those who have no orders (LEFT JOIN).
8️⃣ Find all products that have never been ordered.

🗓 Day 3 – Aggregations & Window Functions

9️⃣ Show total sales amount per region.
🔟 Find the average order amount per customer.
1️⃣1️⃣ Show top 3 highest order amounts overall.
1️⃣2️⃣ Find running total of sales by date.
1️⃣3️⃣ Rank customers based on their total purchase amount.

🗓 Day 4 – Data Cleaning & Transformation

1️⃣4️⃣ Extract only the domain name from customer emails.
1️⃣5️⃣ Find how many days since the last order for each customer.
1️⃣6️⃣ Remove duplicate customers based on Email, keeping the latest JoinDate.
1️⃣7️⃣ Pivot sales data to show total sales per category (rows) vs region (columns).

🗓 Day 5 – Views, Performance & Power BI

1️⃣8️⃣ Create a SalesSummary View combining Orders + Customers + Regions.
1️⃣9️⃣ Merge a dummy ArchivedOrders table with Orders using UNION.
2️⃣0️⃣ Optimize a query by selecting only necessary columns instead of SELECT *.

✅ How to Use in Power BI

Load Customers, Products, Orders, OrderDetails into Power BI.
Create relationships:
- Customers → Orders → OrderDetails → Products
Use the SQL Views you create (like SalesSummary) as data sources.
Build a simple Sales Dashboard (Revenue by Region, Top Products, Top Customers).

Step-by-step T-SQL solutions for all 20 exercises based on the above sample Sales dataset.

🗓 Day 1 – SQL Basics & Filtering

✅ 1. Get all customers who joined after 2023-01-01

SELECT * 
FROM Customers
WHERE JoinDate > '2023-01-01';

✅ 2. List all unique product categories

SELECT DISTINCT Category 
FROM Products;

✅ 3. Show all orders where TotalAmount > 10,000

SELECT * 
FROM Orders
WHERE TotalAmount > 10000;

✅ 4. Find customers who have no email address (NULL)

SELECT * 
FROM Customers
WHERE Email IS NULL;

🗓 Day 2 – Joins

✅ 5. Show all orders with customer full names

SELECT 
    o.OrderID,
    o.OrderDate,
    c.FirstName + ' ' + c.LastName AS CustomerName,
    o.TotalAmount
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;

✅ 6. List products along with their OrderID & Quantity sold

SELECT 
    od.OrderID,
    p.ProductName,
    od.Quantity,
    od.LineTotal
FROM OrderDetails od
JOIN Products p ON od.ProductID = p.ProductID;

✅ 7. Get all customers and their orders (even if no orders)

SELECT 
    c.FirstName + ' ' + c.LastName AS CustomerName,
    o.OrderID,
    o.TotalAmount
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;

✅ 8. Find all products that have never been ordered

SELECT p.ProductName
FROM Products p
LEFT JOIN OrderDetails od ON p.ProductID = od.ProductID
WHERE od.ProductID IS NULL;

🗓 Day 3 – Aggregations & Window Functions

✅ 9. Show total sales amount per region

SELECT 
    c.Region,
    SUM(o.TotalAmount) AS TotalSales
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY c.Region;

✅ 10. Find average order amount per customer

SELECT 
    c.FirstName + ' ' + c.LastName AS CustomerName,
    AVG(o.TotalAmount) AS AvgOrderAmount
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY c.FirstName, c.LastName;

✅ 11. Show top 3 highest order amounts overall

SELECT TOP 3 
    OrderID, TotalAmount
FROM Orders
ORDER BY TotalAmount DESC;

✅ 12. Find running total of sales by date

SELECT 
    OrderDate,
    TotalAmount,
    SUM(TotalAmount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;

✅ 13. Rank customers based on total purchase amount

SELECT 
    c.FirstName + ' ' + c.LastName AS CustomerName,
    SUM(o.TotalAmount) AS TotalSpent,
    RANK() OVER (ORDER BY SUM(o.TotalAmount) DESC) AS RankBySpend
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY c.FirstName, c.LastName;

🗓 Day 4 – Data Cleaning & Transformation

✅ 14. Extract only the domain name from customer emails

SELECT 
    Email,
    RIGHT(Email, LEN(Email) - CHARINDEX('@', Email)) AS EmailDomain
FROM Customers;

✅ 15. Find how many days since the last order for each customer

SELECT 
    c.FirstName + ' ' + c.LastName AS CustomerName,
    DATEDIFF(DAY, MAX(o.OrderDate), GETDATE()) AS DaysSinceLastOrder
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY c.FirstName, c.LastName;

✅ 16. Remove duplicate customers based on Email, keep latest JoinDate

(Using CTE + ROW_NUMBER)

WITH DuplicateCTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Email ORDER BY JoinDate DESC) AS RowNum
    FROM Customers
)
DELETE FROM DuplicateCTE WHERE RowNum > 1;

✅ 17. Pivot sales data to show total sales per category vs region

SELECT *
FROM (
    SELECT 
        c.Region,
        p.Category,
        od.LineTotal
    FROM OrderDetails od
    JOIN Orders o ON od.OrderID = o.OrderID
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN Products p ON od.ProductID = p.ProductID
) AS SourceData
PIVOT (
    SUM(LineTotal) FOR Region IN ([North], [South], [East], [West])
) AS PivotTable;

🗓 Day 5 – Views, Performance & Power BI

✅ 18. Create a SalesSummary View combining Orders + Customers + Regions

CREATE VIEW vw_SalesSummary AS
SELECT 
    o.OrderID,
    o.OrderDate,
    c.FirstName + ' ' + c.LastName AS CustomerName,
    c.Region,
    o.TotalAmount
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;

Then simply:

SELECT * FROM vw_SalesSummary;

✅ 19. Merge a dummy ArchivedOrders table with Orders using UNION

SELECT OrderID, CustomerID, OrderDate, TotalAmount 
FROM Orders
UNION
SELECT OrderID, CustomerID, OrderDate, TotalAmount 
FROM ArchivedOrders;  -- Assume archived table exists

✅ 20. Optimize a query by selecting only necessary columns
❌ Bad (selects all columns, slow)

SELECT * FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID;

✅ Better (only required fields)

SELECT 
    o.OrderID,
    o.OrderDate,
    c.FirstName + ' ' + c.LastName AS CustomerName,
    o.TotalAmount
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;

✅ How to Use This

Run the dataset & sample inserts.
Practice Day-wise queries.
Create a Power BI dashboard using vw_SalesSummary.

Search This Blog

Hey, Hi !