SQL Practice
sqlpractice.co.in
IntermediateSQL project

Ecommerce sales analysis

You are the analyst for a small ecommerce operation. Management needs one compact report that explains what sold, who bought it and whether revenue is changing. You will explore the existing Customers, Orders, Products and Employees tables, then build five queries whose results can be checked independently.

Skills you will practise

  • multi-table joins
  • conditional aggregation
  • GROUP BY and HAVING
  • CTEs
  • date grouping

Project requirements

  • Count only Shipped orders when calculating realised revenue.
  • Keep customers with no shipped orders in the customer summary.
  • Give every calculated column an explicit alias.
  • Use deterministic ordering whenever the report ranks rows.

Build it in stages

  1. 1

    Establish the baseline

    Return shipped order count, total shipped revenue and average shipped order value in one row. Use CASE or a WHERE clause consistently so cancelled and pending orders never enter the totals.

  2. 2

    Rank product categories

    Join Orders to Products, group by category and rank categories by shipped revenue. Include order count as a second measure so a category with one unusually large order is easy to spot.

  3. 3

    Build the customer report

    Start from Customers and LEFT JOIN Orders. Return every company, shipped order count and shipped spend, including zeroes for customers without a shipped order.

  4. 4

    Measure monthly movement

    Extract the month from order_date, then group shipped orders by month. Sort chronologically rather than by revenue.

  5. 5

    Write an executive summary query

    Use CTEs to combine the strongest category, highest-value customer and busiest salesperson into a concise final result. Keep each CTE responsible for one question.

Starter code

 SQLRun
-- 1. Baseline shipped-order metrics
SELECT
  COUNT(*) AS shipped_orders,
  SUM(order_total) AS shipped_revenue
FROM Orders
WHERE status = 'Shipped';

-- Continue with category, customer, monthly and summary queries.

Open the SQL playground

Expected result

A reproducible five-part report: baseline metrics, ranked categories, a complete customer table, chronological monthly totals and a final management summary. Each section should be understandable without reading the SQL.

Progressive hints

Hint 1

NULL totals from a LEFT JOIN can be displayed as zero with COALESCE.

Hint 2

Apply the shipped condition inside CASE when the outer join must retain unmatched customers.

Hint 3

Build and run each CTE alone before combining them.

Solution guidance

Show the approach after you attempt the project

A strong solution separates scope from presentation. Filter realised revenue at the earliest safe point, aggregate at exactly the grain named by each report, and add readable aliases and stable ordering last. For the customer report, filtering in WHERE would remove customers with no matching shipment, so conditional aggregation belongs inside SUM and COUNT. The final CTE report stays maintainable because each business question can be verified independently.

Related learning