SQL exercise 1

Exercise · SQL Sales Analysis — Ctrl+Speak

Exercise #2 · SQL Sales Analysis + English Explanation

Data Engineering · SQL · Speaking/Writing

Assignment & Scenario

SQL · Communication

Scenario: You are a data engineer tasked with analyzing sales. Using the orders table (already loaded in memory), write SQL to:

Compute total revenue per region.
Find the top product by revenue in each region.
Bonus
Produce a monthly revenue trend (YYYY-MM).

Then deliver a short English status update summarizing your findings (120–180 words).

SQL Pad (orders table is ready)

Tip: Press Ctrl + Enter to execute.

<!-- Starter queries you can copy -->
-- Revenue by region
SELECT region, SUM(unit_price*quantity) AS revenue
FROM orders
GROUP BY region
ORDER BY revenue DESC;

-- Top product per region (by revenue)
WITH rev AS (
  SELECT region, product, SUM(unit_price*quantity) AS revenue,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(unit_price*quantity) DESC) AS rn
  FROM orders
  GROUP BY region, product
)
SELECT region, product, revenue
FROM rev
WHERE rn = 1
ORDER BY revenue DESC;

-- Monthly revenue trend (YYYY-MM)
SELECT strftime('%Y-%m', order_date) AS month, SUM(unit_price*quantity) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

🎤 Speaking Instructions (in-class) how to perform

Title: “SQL sales analysis — status update.”
Scope: “I analyzed the orders table to compare regions, top products, and monthly trend.”
Key metrics: State revenue by region (ranked) and the top product per region.
Trend: One sentence about month-over-month changes.
Insights: One sentence explaining a possible reason (channel, seasonality, product mix).
Next steps: Suggest a follow-up (e.g., join with marketing/campaigns).

📖 Vocabulary definitions

aggregate — to group and summarize data.
window function — a function that performs a calculation across a set of rows related to the current row.
partition — the subset a window function operates over (e.g., per region).
metric — a numeric measure (e.g., revenue, quantity).
trend — a pattern of change over time.
outlier — a value far from others in the dataset.
granularity — the level of detail (daily, monthly, by product).

🧩 Collocations natural pairings

calculate revenue / growth
group by region / product
drill down into channels
compare month over month
flag an anomaly / outlier
join with campaigns / inventory
summarize findings for stakeholders

🗣️ Idioms & Phrasal Verbs natural speech

move the needle — create a noticeable change.
rule out — eliminate a possibility.
zoom in on — focus closely on a detail.
back up — support with evidence.
pick up — increase (e.g., “sales picked up in Q2”).
level off — stop increasing/decreasing and remain steady.

🧪 Model Answer (spoken style) example

“Scope: I analyzed the orders table to compare regions, top products, and the monthly trend. APAC generated the highest revenue overall, followed by the Americas and EMEA. In APAC, the top product was Widget-A, contributing the largest share of revenue; in the Americas, Service-Pro led the region. Month over month, revenue increased steadily from May to July, then leveled off slightly in August. My main takeaway is that product mix differs by region, which likely explains the performance gap. Next steps: join with marketing campaigns to check whether promotions or channels are driving regional spikes, and validate whether returns or discounts affect the final revenue figures.”