Online Shopping Analytics in SQL Server
T-SQLSQL ServerSubqueriesJoinsAggregationStored procedures
A SQL Server database for an online shop covering customers, orders, order lines, products and payments. After connecting the imported CSV tables with foreign keys, I wrote T-SQL to answer ten business questions using correlated subqueries, multi-table joins, grouped aggregates, TOP-N ranking, VAT calculations and a stored procedure that discounts high-value payments.
5Tables
4Foreign keys
9Analytical queries
1Stored procedure
The problem
An online retailer wants answers from its order data: which customers spend in a given range, what sells and how much revenue each product earns, which orders are the most valuable, and which payments qualify for a discount. The first step is a relational model the data can be trusted in.
The data
- Five tables imported from CSV files: Customers, Orders, Order_items, Products and Payments.
- Customers carry a country, which the UK and Australia queries filter on; Products carry a category such as Electronics; Payments record the payment method and the amount paid.
Approach
- Connect the tablesFour foreign keys link Orders to Customers, Order_items to Orders and to Products, and Payments to Orders, so the imported CSV data becomes a relational model with enforced integrity.
- Filter with subqueriesEXISTS finds customers with an order line of 500 to 1,000, NOT EXISTS finds customers who have paid but have no order items, and IN selects the payments for orders that contain Electronics.
- Aggregate by customer and productTotal paid by UK customers whose orders hold more than three distinct products, units sold per product, and products sold more than ten times with their revenue (GROUP BY, HAVING, ORDER BY).
- Rank with TOP NThe two highest payments from the UK and Australia after adding 12.2% VAT and rounding, and the three most expensive orders by total value.
- Automate a pricing ruleApplyHighValueDiscount is a stored procedure that takes 5% off payments of 17,000 or more for orders containing a laptop or smartphone, and prints how many rows it changed.
- Join for detailA four-table join lists every customer’s orders with the product names and quantities.
Schema and queries
| Task | Question answered | Technique |
|---|---|---|
| 2 | Customers (name and country) with an order line totalling 500 to 1,000 | Correlated EXISTS, BETWEEN |
| 3 | Total paid by UK customers whose orders hold more than three distinct products | JOIN, IN subquery, HAVING COUNT(DISTINCT) |
| 4 | Two highest payments from the UK and Australia after 12.2% VAT, rounded | TOP 2, ROUND, IN |
| 5 | Total quantity sold for each product | JOIN, SUM, GROUP BY |
| 6 | Apply a 5% discount to qualifying high-value payments | Stored procedure, UPDATE … FROM with joins, @@ROWCOUNT |
| 7a | Customers who have paid but have no order items | EXISTS and NOT EXISTS |
| 7a | Payment details for orders that contain Electronics | IN subquery |
| 7b | Every customer’s orders with product names and quantities | Four-table JOIN |
| 7c | The three most expensive orders | TOP 3, SUM, GROUP BY |
| 7d | Products sold more than ten times, with total revenue | GROUP BY, HAVING, ORDER BY |
Key findings
- Adding foreign keys after the import turns loose CSV tables into a model where an order cannot exist without a customer and a payment cannot point at a missing order.
- EXISTS and NOT EXISTS answer “has any” and “has none” questions without the duplicate rows a plain join would produce.
- Counting distinct products, not rows, means a repeated line for the same product does not push an order over the “more than three products” threshold.
- Putting the discount rule in a stored procedure keeps pricing logic in one place and reports how many payments it touched.
Next steps
- Make ApplyHighValueDiscount idempotent: it multiplies by 0.95 on every run, so repeated runs compound the discount. Record the original amount or a discount flag first.
- Add primary keys and indexes on customer_id, order_id and product_id, and confirm the gain with execution plans.
- Wrap the reporting queries in views or parameterised procedures so a dashboard such as Power BI can reuse them.