Analytics and capstone
Capstone: sales analytics
Turn learning into a verifiable report.
Understand the concept
Write a dashboard contract first: Paid orders only, revenue from line prices, one total per order and periods based on order date. Preserve customers with no orders in the customer view. Customer counts and order counts are separate measures.
Deliver three things: correct queries, a short explanation of grain and NULL decisions, and actual-plan/IO observations. The lab expects nine paid orders and revenue of 95090. Two Pending and one Cancelled order are excluded. If reconciliation fails, investigate join fan-out and status filters before drawing charts. Extend with monthly cohorts or customer RFM without inventing missing traffic or profit data.
Connect knowledge with a reproducible deliverable.
Trusting a dashboard without reconciliation totals.
One topic. Three levels of depth.
SELECT COUNT(*) AS PaidOrders FROM lab.SalesOrder WHERE Status = 'Paid';The first checkpoint is nine.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Build monthly paid-order and revenue totals, then reconcile.
Show the solution and explanation
WITH T AS (SELECT OrderId, SUM(Quantity * UnitPrice) AS Total FROM lab.OrderItem GROUP BY OrderId) SELECT DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1) AS MonthStart, COUNT(*) AS PaidOrders, SUM(t.Total) AS Revenue FROM lab.SalesOrder AS o JOIN T AS t ON t.OrderId = o.OrderId WHERE o.Status = 'Paid' GROUP BY DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1) ORDER BY MonthStart;January 2/33760, February 1/1350, March 3/18400, April 3/41580. Total 9/95090.