This project shows how I solve real-world business problems, with a focus on delivering actionable recommendations that drive business decisions. Using the Brazilian E-Commerce Public Dataset by Olist, I simulated five common scenarios that data analysts face across operations, marketing, and customer service teams.
Data: Brazilian E-Commerce Public Dataset by Olist
Tools: SQL (SQLite)
When deciding how much data to pull or how detailed to make my analysis, I try to keep the big picture in mind, not just the specific question that's asked.
- Deadline: If the results are needed quickly, I stick to the basics so I can deliver fast.
- What does the stakeholder like? Some people just want a quick answer and are done, but others like to dig deeper and might ask for extra details later. If I think there will be follow-up questions, I include a bit more context even if it's not asked for yet.
- Recurring requests: If I feel like it might come up again, I'll check if the team wants a repeatable report or dashboard, so we're set for the future.
- Looking at the bigger picture: I want my work to help the whole team, not just solve one small problem. So I try to spot patterns, think about how my work fits into our process, and make things smoother for everyone.
I do my best to balance giving enough information to help others make good decisions, without overwhelming them or wasting time. If I'm not sure how much detail is needed, I check with the stakeholder and try to offer options. My goal isn't just to answer a question, but to make things easier, smarter, and more connected for our team.
Which sellers have the fastest average delivery time in the last 3 months?
- What make them the fastest seller?
Fast delivery is a supply-side retention driver, customers who get orders quickly are more likely to reorder. But before optimizing for speed, I want to know why fast sellers are fast. If it's geography (they're closer to customers), the fix is logistics. If it's behavior (how they handle fulfillment), it's coachable. The analysis below sets up that distinction by including city and state alongside delivery time.
SELECT
s.seller_id, s.seller_city, s.seller_state,
COUNT(DISTINCT oi.order_id) AS orders_fulfilled,
-- Calculate average delivery time (purchase to delivery)
ROUND(AVG(JULIANDAY(o.order_delivered_customer_date) -
JULIANDAY(o.order_purchase_timestamp)), 1) AS avg_delivery_days
FROM sellers s
JOIN order_items oi ON s.seller_id = oi.seller_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
AND o.order_estimated_delivery_date IS NOT NULL
-- Last 3 months from latest date in dataset
AND o.order_purchase_timestamp >= DATE((SELECT MAX(order_purchase_timestamp)
FROM orders), '-90 days')
GROUP BY s.seller_id, s.seller_city, s.seller_state
HAVING orders_fulfilled >= 20 -- Minimum volume for statistical significance
ORDER BY avg_delivery_days ASC -- Fastest first
LIMIT 20;
In production, I'd consider adding an index on order_purchase_timestamp and order_status to avoid full table scans at scale.
- The fastest sellers are consistently delivering in 5 to 6 days on average.
- Most top sellers by speed are in São Paulo (SP), especially São Paulo city.
- High volume doesn't slow everyone down (one seller fulfilled 104 orders at an average of 5.3 days).
- Investigate what the top sellers do well (how do they handle shipping/logistics?) and share those practices.
- Offer support or coaching to sellers with delivery averages over 6 days.
- Test if new delivery partnerships or better options would help slow sellers outside big cities.
- Analyze whether geographic distance is a key driver of delivery speed. If so, expanding warehouse or fulfillment locations could help sellers outside major cities compete.
We're getting complaints about late deliveries in São Paulo. Is it a real pattern or just a few people being loud?
- Is São Paulo really worse than other places?
- How many customers affected?
- How late are the deliveries?
I've compared São Paulo against other big cities with over 200 orders (using only the last 30 days).
"A few people being loud" vs "a real pattern" is actually a testable question (we need a baseline to compare against). My instinct here is to compare São Paulo not just against itself over time, but against other high-volume cities. If São Paulo's late rate is within normal range for a city its size, the complaint volume might just reflect its order volume. If it's an outlier, then that's a different conversation.
SELECT
c.customer_city,
COUNT(DISTINCT o.order_id) as total_orders,
SUM(CASE
WHEN JULIANDAY(o.order_delivered_customer_date) >
JULIANDAY(o.order_estimated_delivery_date)
THEN 1 ELSE 0 END) as late_orders,
ROUND(SUM(CASE
WHEN JULIANDAY(o.order_delivered_customer_date) >
JULIANDAY(o.order_estimated_delivery_date)
THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as late_pct,
ROUND(AVG(CASE
WHEN JULIANDAY(o.order_delivered_customer_date) >
JULIANDAY(o.order_estimated_delivery_date)
THEN JULIANDAY(o.order_delivered_customer_date) -
JULIANDAY(o.order_estimated_delivery_date)
ELSE 0 END), 1) as avg_days_late_when_late
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
-- Last 30 days
AND o.order_delivered_customer_date >= DATE('2018-08-01', '-30 days')
GROUP BY c.customer_city
-- Other major cities with total orders over 200
HAVING total_orders>200
ORDER BY late_pct DESC;
- São Paulo had a late delivery rate of 12.2%, which is much higher than Rio de Janeiro (7.4%) and most other large cities (below 5%).
- Over 300 customers were impacted in the last month.
- The average delay was about half a day, but São Paulo saw late orders much more often.
- Dig into the root cause in São Paulo. Understanding if it is specific sellers, specific carriers, or certain product types causing the delays. Pinpointing the source will help Customer Service and Operations prioritize fixes rather than reviewing everything at once.
- Keep tracking late deliveries in all cities to spot new issues early.
Who are the top ten spenders in Q1 2018?
- What do these top customers have in common?
- Who are the top 10 spenders?
- Where are they from?
Marketing probably wants something targetable (a profile they can build a campaign around). The risk with "top 10 spenders" is that it's too small a sample to be statistically meaningful, and high single-purchase values could just be category effects (someone bought a laptop). I'll answer the question as asked, but flag if the findings don't actually support a targeted campaign.
WITH q1_customer_spending AS (
SELECT
c.customer_unique_id, c.customer_city, c.customer_state,
COUNT(DISTINCT o.order_id) AS num_orders,
SUM(p.payment_value) AS total_spend
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN payments p ON o.order_id = p.order_id
WHERE o.order_status = 'delivered'
AND o.order_purchase_timestamp >= '2018-01-01'
AND o.order_purchase_timestamp < '2018-04-01'
GROUP BY c.customer_unique_id, c.customer_city, c.customer_state
)
SELECT
customer_unique_id, customer_city, customer_state, num_orders,
ROUND(total_spend, 2) AS total_spend,
ROUND(total_spend/num_orders, 2) AS avg_order_value
FROM q1_customer_spending
ORDER BY total_spend DESC
LIMIT 10;
- All ten top spenders in Q1 2018 made just one purchase each, ranging from $2,204 to $4,175.
- These big spenders are spread across different cities, regions, and product categories. There is no clear shared profile emerged.
- The absence of common traits is itself a finding: high-value purchases appear to be scattered outliers rather than a replicable customer segment.
- Rather than marketing to just these ten individuals, consider expanding the analysis to the top 100 or top 1,000 spenders to find statistically meaningful patterns by clustering them by category, region, or timing that could actually guide campaign targeting.
- Once a broader segment is identified, test re-engagement with personalized offers based on purchase category and monitor whether one-time high spenders can be converted to repeat buyers.
- Share findings with the product team to help them understand what high-value customers bought may reveal bundling or upsell opportunities worth building into the platform.
Which cities brought in the most sales in 2017?
This one is straightforward, but city-level revenue on its own can be misleading since São Paulo will always win just because of population. The more useful question is revenue per capita or growth rate, which tells you where to invest next. I'll deliver the ranking as asked and flag this for follow-up.
SELECT
c.customer_city, c.customer_state,
ROUND(SUM(p.payment_value), 2) AS total_revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN payments p ON o.order_id = p.order_id
WHERE o.order_status = 'delivered'
AND strftime('%Y', o.order_purchase_timestamp) = '2017'
GROUP BY c.customer_city, c.customer_state
ORDER BY total_revenue DESC
LIMIT 5;
- São Paulo (SP) is the top city by total revenue in 2017, with over $850K in sales, followed by Rio de Janeiro (RJ) at over $540K.
- The top five cities accounted for a big share of all sales, confirming that big urban areas lead in e-commerce.
- Focus marketing on these top cities to make the most of where demand is already high.
- Study what's working in these places and try to apply those lessons in other growing cities.
We ran a discount campaign on electronics July 15-21. Did it work?
- Did revenue and orders increase during the campaign?
- Did things slow down after?
"Did it work?" needs a clearer definition before I can answer it. Work for revenue? Orders? Margin? I'll look at all three, but I also want to check for a post-campaign dip since discounts sometimes just pull forward demand rather than create new demand. If post-campaign numbers drop below pre-campaign baseline, that's a red flag worth surfacing even if the campaign week numbers look good.
- Compared campaign week to the week before and after.
- Looked at those three periods for sales and pricing.
WITH campaign_data AS (
SELECT
o.order_id, o.order_purchase_timestamp,
oi.order_item_id, oi.price,
p.product_category_name,
CASE
WHEN DATE(o.order_purchase_timestamp)
BETWEEN '2018-07-08' AND '2018-07-14' THEN 'Pre-campaign'
WHEN DATE(o.order_purchase_timestamp)
BETWEEN '2018-07-15' AND '2018-07-21' THEN 'Campaign week'
WHEN DATE(o.order_purchase_timestamp)
BETWEEN '2018-07-22' AND '2018-07-28' THEN 'Post-campaign'
END as period
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_status = 'delivered'
AND p.product_category_name
IN ('eletronicos', 'informatica_acessorios', 'pcs', 'tablets_impressao_imagem')
AND DATE(o.order_purchase_timestamp) BETWEEN '2018-07-08' AND '2018-07-28'
)
SELECT
period,
COUNT(DISTINCT order_id) as num_orders,
COUNT(order_item_id) as items_sold,
ROUND(SUM(price), 2) as gross_revenue,
ROUND(AVG(price), 2) as avg_item_price,
ROUND(SUM(price) / COUNT(DISTINCT order_id), 2) as avg_order_value
FROM campaign_data
WHERE period IS NOT NULL
GROUP BY period
ORDER BY
CASE period
WHEN 'Pre-campaign' THEN 1
WHEN 'Campaign week' THEN 2
WHEN 'Post-campaign' THEN 3
END;
- Orders jumped from 86 to 130 during the campaign, and even hit 144 after the campaign ended.
- Revenue go from $8.6K to $20.9K post-campaign (more than doubled).
- Discounts lowered the average item price, but revenue and order counts still went up.
- Try similar short-term discount campaigns as they're effective for boosting both orders and revenue.
- Dig into why post-campaign numbers spiked. Were those returning buyers, or new ones? The answer points in pretty different directions strategically.
These five scenarios show different ways I approach business questions with SQL. Sometimes I keep it simple for quick answers, sometimes I dig deeper when I think stakeholders need more context. The goal is always to turn data into decisions that people can actually use.
made with ✨ + 🌿 + 💛 by Chia Chang