Stephen's Data Deck - SQL: Walmart

SQL: Walmart_Sales_Analysis

Project Overview

Background

Walmart operates one of the world’s largest retail networks, with store performance shaped by a complex mix of internal factors and external forces like seasonal demand, macroeconomic conditions, weather, and regional economic health. This project applied SQL-based analysis to a multi-year transactional dataset to surface the patterns behind that complexity and translate them into actionable commercial intelligence.

Objective

The analysis set out to answer seven business questions: identifying top-performing stores, quantifying the revenue impact of holiday periods, measuring the influence of macroeconomic indicators on sales, pinpointing the highest-performing months, tracking weekly market leaders, assessing the effect of weather on spending, and flagging structurally struggling stores for targeted intervention.

Data Source

The dataset comprised 6,435 weekly sales records across 45 Walmart stores from 2010 to 2012. Variables included weekly sales, holiday flag, temperature, fuel price, Consumer Price Index (CPI), and unemployment rate , enabling both internal performance analysis and external factor correlation.

Methodology

All analysis was conducted in MySQL using aggregation, grouping, conditional filtering, window functions, correlation logic, and multi-condition WHERE clauses. Techniques included revenue ranking with GROUP BY and ORDER BY, seasonal performance indexing using RANK() OVER (PARTITION BY year), Pearson-style correlation approximation for macroeconomic variables, temperature-based segmentation, and a composite distress filter combining below-average sales, high unemployment, and above-average fuel prices in a single query.

Q1 — Top 10 Stores by Total Revenue

Store 20 leads the network with $301.4 million in total sales, the only store to cross the $300M threshold. Stores 4 ($299.5M) and 14 ($288.9M) form a close top tier, while Store 39 closes the top 10 at $207.4M, a $94M gap from the leader. The top 10 stores collectively dominate network revenue, and their consistent appearance in weekly and seasonal rankings confirms structural, not circumstantial, performance advantages.

Key insight: Stores 20, 4, and 14 are the network’s anchor performers. Resource allocation and best-practice sharing should be anchored in this group.

Q2 — Holiday Impact on Revenue

Holiday weeks generate an average of $1,122,887 versus $1,041,256 in non-holiday weeks, a 7.8% revenue premium. The uplift is consistent and statistically meaningful, confirming that holiday periods are a genuine demand driver across the network, not an outlier effect from a handful of stores.

Key insight: Holiday weeks reliably outperform. Inventory readiness, staffing, and promotional investment in these periods deliver measurable returns.

Q3 — Macroeconomic Factors and Sales

Correlation analysis across three external variables produced weak but directionally consistent results. Fuel price (r = 0.01) showed virtually no relationship with sales. CPI (r = −0.07) reflected mild inflation sensitivity. Unemployment (r = −0.11) was the strongest factor, negative and the most impactful of the three, indicating that workforce conditions have a measurable drag on consumer spending. That all three correlations are weak is itself a finding: Walmart’s value positioning creates natural resilience to macroeconomic pressure.

Key insight: Unemployment is the macro variable most worth monitoring at the store level. Fuel price can be deprioritised as a demand signal.

Q4 — Highest Performing Months

December is the undisputed peak month across all years, $288.8M in 2010, $288.1M in 2011, driven by holiday shopping. July and April consistently rank in the top three across multiple years, suggesting mid-year and spring demand spikes beyond the holiday season. January is the weakest month every year ($163.7M in 2011, $168.9M in 2012), reflecting the predictable post-holiday pullback. The within-year ranking methodology used here effectively isolates seasonal patterns by normalising for year-on-year growth.

Key insight: December, July, and April are the three months that most reward operational readiness. January requires cost management rather than demand stimulation.

Q5 — Weekly Market Leaders

Week 51 (mid-December) consistently produces the network’s highest single-week revenues across all years. In 2010, Store 14 peaked at $3.82M in a single week; in 2011, Store 4 led at $3.68M. A stable elite group; Stores 14, 20, 10, 4, 13, and 2 dominates the weekly leaderboard regardless of year, confirming that top-store performance is structural and persistent. At the other end, Stores 33, 36, 38, and 44 consistently appear at the bottom of weekly rankings, signalling a chronic performance gap within the network.

Key insight: Weekly rankings reveal the same elite stores year after year. The performance gap between top and bottom stores is not closing, it warrants a structural, not tactical, response.

Q6 — Weather and Customer Spending

Temperature-segmented revenue analysis shows a clear inverse relationship between heat and spending. Cold weather averages $1,081,396 per week, mild weather $1,065,619, and hot weather $1,005,917, a $75,479 drop from cold to hot, representing a 7% decline. The effect is real but moderate, and likely compounded by the fact that cold periods overlap with high-spending holiday months identified in Q4.

Key insight: Weather is a secondary demand signal. Its influence is most relevant when planning store-level staffing and inventory for summer periods.

Q7 — Struggling Stores: A Multi-Factor Distress Screen

A composite SQL query identified 9 stores simultaneously meeting three distress criteria: below-average sales, high unemployment, and above-average fuel prices. Store 33 is the most critical case, averaging just $259,862 per week, less than a quarter of top-store performance. Stores 38 ($385,732) and 29 ($539,451) are close behind. Stores 12 and 38 face the highest unemployment rates at 13.12%, nearly double some peers, suggesting severe demand-side constraints. The clustering of fuel prices between $3.42–$3.61 across all nine stores implies geographic concentration of distress and these are likely stores in the same high-cost, economically disadvantaged regions.

Key insight: Stores 33, 38, and 29 require urgent review. The distress screen built in this analysis can be operationalised as a recurring monitoring query to flag at-risk stores as economic conditions shift.

Closing Observations

This analysis demonstrates that Walmart’s store performance is driven by a layered combination of factors: structural store capacity, seasonal demand cycles, and the macroeconomic conditions of the surrounding region. The SQL methodology applied here; spanning aggregation, window functions, correlation analysis, seasonal ranking, and multi-condition distress filtering was designed to extract insight at each of these layers.

Two findings stand out for their practical value. First, the holiday revenue premium is consistent enough to be treated as a planning certainty, not a forecast. Second, unemployment is the most actionable macroeconomic variable for predicting local demand risk.

Codes

select *

from walmart_sales;

## Q1:Identify the top 10 stores with the highest total sales across the entire period.

select store,round(sum(weekly_sales),2) as revenue

from walmart_sales

group by store

order by revenue desc

limit 10;

## Q2: Does holiday significantly influence revenue?

select holiday_flag,

case when holiday_flag = 0 then 'non-holiday week' else 'holiday week' end as HolidayFlag,

round(avg(weekly_sales),2) as avg_revenue

from walmart_sales

group by holiday_flag

order by avg_revenue desc;

## Q3:What macroeconomic factors affect retail performance.

select round((avg(weekly_sales*fuel_price)-avg(weekly_sales)*avg(fuel_price))/(stddev(weekly_sales)*stddev(fuel_price)),2) as weekly_sales_vs_fuel_price,

round((avg(weekly_sales*cpi)-avg(weekly_sales)*avg(cpi))/(stddev(weekly_sales)*stddev(cpi)),2) as weekly_sales_vs_cpi,

round((avg(weekly_sales*unemployment)-avg(weekly_sales)*avg(unemployment))/(stddev(weekly_sales)*stddev(unemployment)),2) as weekly_sales_vs_unemployment

from walmart_sales;

## Q4: Identify the highest performing months historically.

## Change column date to date type

UPDATE walmart_sales

SET date = STR_TO_DATE(date, '%d-%m-%Y');

alter table walmart_sales

modify column date DATE;

## back to the question

with month_and_year as (SELECT month(date) as month,year(date) as year,round(sum(Weekly_Sales),2) as sales

FROM walmart_sales

group by 2,1

)

select month,year,sales,rank() over(partition by year order by sales) performance

from month_and_year;

## Q5: Identify weekly market leaders.

with week as (select store, weekofyear(date) as week, year(date) as year,round(sum(weekly_sales),2) as revenue

from walmart_sales

group by 1,2,3

)

select store,year,week,revenue,rank() over(partition by year order by revenue desc) as position

from week;

## Q6: Calculate week-over-week sales growth per store.

with week_sales as (select store, weekofyear(date) as week, year(date) as year,round(sum(weekly_sales),2) as revenue

from walmart_sales

group by 1,2,3

week_over_week as (select store,week,year,revenue,lag(revenue) over (partition by store order by year) as prev_week_sales

from week_sales

)

select store,week,year,revenue,prev_week_sales,ROUND(revenue - prev_week_sales, 2) sales_difference

from week_over_week

order by 1,3,2;

##Q7: Determine whether weather affects customer spending.

select case

when temperature < 40 then 'cold'

when temperature between 40 and 70 then 'mild'

else 'hot'end as temperature_group,

round(avg(weekly_sales),2) as avg_revenue

from walmart_sales

group by temperature_group

order by 2 desc;

## Q8: Which stores are struggling.

/*Identify store that meet the following criteria:

- Below-average sales

- High unemployment regions

- Above-average fuel prices */

SELECT

store,

ROUND(AVG(weekly_sales),2) AS avg_sales,

ROUND(AVG(unemployment),2) AS avg_unemployment,

ROUND(AVG(fuel_price),2) AS avg_fuel_price

FROM walmart_sales

GROUP BY store

HAVING

avg_sales < (SELECT AVG(weekly_sales) FROM walmart_sales)

AND avg_unemployment > (SELECT AVG(unemployment) FROM walmart_sales)

AND avg_fuel_price > (SELECT AVG(fuel_price) FROM walmart_sales);

Page updated

Google Sites

Report abuse