A full-stack pricing intelligence platform simulating the day-in-the-life of a Pricing Analytics Lead at an automotive aftermarket parts company. Built to demonstrate production-grade analytics engineering skills across SQL, BI, data modeling, and AI-assisted analysis.
Portfolio project by Steve Lopez | GitHub: BigZeeke
This project replicates the analytical environment described in senior pricing analyst job descriptions — including data from ERP, WMS, quoting systems, contract management, and e-commerce — and delivers a two-tab Streamlit application:
| Tab | Description |
|---|---|
| 📊 Pricing Dashboard | 7 live analytical views powered by the SQL query library |
| 🤖 AI Pricing Assistant | Natural language → SQL → results, powered by Claude API |
12-table SQLite database modeling 4 source systems and ~14,500 synthetic rows:
ERP → dim_sku, dim_customer, dim_region, fact_orders
WMS → fact_returns, fact_inventory
Quoting → fact_quotes, dim_competitor_price
Contracts → fact_contracts
e-Commerce → fact_ecomm_orders
Governance → dim_pricing_matrix, fact_discount_exceptions
dim_sku ──────────┬── fact_orders ──── dim_customer ── dim_region
├── fact_returns
├── fact_inventory
├── fact_quotes ───── dim_competitor_price
├── fact_contracts
├── fact_ecomm_orders
└── fact_discount_exceptions
dim_pricing_matrix → governs → fact_discount_exceptions
Eight production-quality named queries, each demonstrating specific advanced SQL patterns:
| Query File | Techniques | Business Purpose |
|---|---|---|
margin_waterfall_by_customer.sql |
Multi-step CTE chain, conditional aggregation, window SUM | Decompose list → pocket margin per customer across all concession layers |
price_realization_by_sku.sql |
RANK, NTILE, LAG, period-over-period comparison | Surface SKUs and regions giving up the most vs. list price |
quote_win_rate_by_segment.sql |
Conditional aggregation, rolling AVG window, correlated subquery | Win/loss analysis by segment with discount discipline scoring |
price_elasticity_by_category.sql |
LAG for deltas, point elasticity math, prescriptive classification | Identify which categories can absorb price increases vs. which are price-sensitive |
competitive_gap_analysis.sql |
Multi-source JOIN, ROW_NUMBER for latest price, RANK within category | Compare our prices vs. competitor market reference; flag revenue at risk |
discount_exception_monitor.sql |
Cumulative window SUM, severity scoring, governance joins | Daily exception report: policy violations by severity and approval status |
cost_inflation_passthrough.sql |
LAG for QoQ deltas, passthrough rate calculation | Track how much cost inflation was recovered via price adjustments |
mix_shift_impact.sql |
Period bridge decomposition, volume/mix/price effect math | Decompose margin change into three drivers: volume, mix, and price |
-- CTEs (multi-step chain)
WITH order_economics AS (...),
customer_waterfall AS (...),
waterfall_with_margins AS (...),
ranked AS (...)
SELECT ...
-- Window functions
RANK() OVER (PARTITION BY segment ORDER BY win_rate_pct DESC)
NTILE(4) OVER (ORDER BY avg_realization_rate DESC)
LAG(avg_cost) OVER (PARTITION BY sku_id ORDER BY quarter)
SUM(margin_at_risk) OVER (PARTITION BY customer_id ORDER BY exception_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AVG(...) OVER (PARTITION BY category ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- Complex joins across 4 source systems
FROM fact_orders o
JOIN dim_sku s ON o.sku_id = s.sku_id
JOIN dim_customer c ON o.customer_id = c.customer_id
LEFT JOIN dim_competitor_price cp ON s.sku_id = cp.sku_id
LEFT JOIN fact_contracts fc ON c.customer_id = fc.customer_id
-- Query optimization patterns
-- Correlated subquery for dominant value (top loss reason per segment)
-- ROW_NUMBER() to deduplicate latest competitor price per SKU
-- NULLIF() guards on all division operations
-- DATE() filters for period scopingLayered bar chart decomposing revenue from list price → pocket margin across the top 15 customers. Shows exactly which concession layers (discounts, promos, rebates, freight, payment terms) are most erosive.
Category × Region grid showing average invoice price as a % of list. Instantly surfaces which region/category combinations are most discounted.
Side-by-side: win rate vs. portfolio average, plus discount at win vs. discount at loss — exposing whether the team is discounting too deep without winning.
Bar chart of elasticity coefficients with classification and prescriptive recommendation per category (raise / hold / protect).
Live governance table of pending exceptions flagged Critical / High / Medium, sorted by margin at risk.
Scatter plot of category-level price gap vs. revenue at risk, plus top 10 SKUs by exposure with recommended action.
Side-by-side: QoQ cost vs. price change (inflation passthrough), and margin bridge decomposed into volume / mix / price effects.
The assistant tab uses the Claude API with a domain-aware system prompt containing the full schema, metric definitions, and SQLite syntax rules.
Capabilities:
- Translates natural language pricing questions into executable SQL
- Runs the query against the live database and renders results inline
- Maintains multi-turn conversation context
- Pre-loaded with 5 suggested questions to demonstrate range
Example questions:
"Which customers have the lowest pocket margin this quarter?"
"What discount level actually wins quotes for Tier 1 Distributors?"
"Which CV Axle SKUs are priced significantly above competitors?"
"Show me categories where cost increased but price didn't follow."
"Which customers have pending discount exceptions over $500 at risk?"
automotive-pricing-analytics/
│
├── data/
│ └── generate_data.py # Synthetic data generator (all 12 tables)
│
├── database/
│ ├── schema.sql # Standalone DDL for all tables
│ └── pricing_analytics.db # Generated SQLite database
│
├── queries/
│ ├── margin_waterfall_by_customer.sql
│ ├── price_realization_by_sku.sql
│ ├── quote_win_rate_by_segment.sql
│ ├── price_elasticity_by_category.sql
│ ├── competitive_gap_analysis.sql
│ ├── discount_exception_monitor.sql
│ └── cost_inflation_passthrough_and_mix_shift.sql
│
├── app/
│ └── app.py # Streamlit application
│
├── .streamlit/
│ └── config.toml # Theme and server config
│
├── requirements.txt
└── README.md
git clone https://github.com/BigZeeke/automotive-pricing-analytics.git
cd automotive-pricing-analyticspip install -r requirements.txtpython data/generate_data.pyOutput: database/pricing_analytics.db with ~14,500 rows across 12 tables.
export ANTHROPIC_API_KEY="your-key-here"streamlit run app/app.pyNavigate to http://localhost:8501
| Layer | Technology |
|---|---|
| Database | SQLite (portable; schema mirrors Azure SQL / Databricks patterns) |
| Data Generation | Python, Faker, NumPy, Pandas |
| Analytics | Advanced SQL — CTEs, window functions, complex joins |
| Visualization | Plotly (waterfall, heatmap, scatter, bar) |
| Application | Streamlit |
| AI Layer | Anthropic Claude API (claude-sonnet) |
This project was designed to mirror the technical requirements of senior pricing analytics and analytics engineering roles:
- ✅ Advanced SQL: multi-step CTEs, window functions (RANK, NTILE, LAG, SUM OVER, AVG OVER), complex multi-source joins, query optimization patterns
- ✅ Pricing analytics: margin waterfall, pocket margin, price realization, elasticity modeling, competitive gap, discount governance, mix/shift bridge
- ✅ Data modeling: star schema across 4 source systems (ERP/WMS/quoting/e-commerce)
- ✅ BI development: Plotly charts, heatmaps, waterfall, scatter — styled for executive dashboards
- ✅ AI integration: NL-to-SQL with domain-aware prompting, multi-turn conversation, live query execution
- ✅ Data governance: exception monitoring, approval matrix, audit trail patterns
- ✅ Python: OOP-style data generation, modular query loading, Streamlit app architecture
Steve Lopez
- LinkedIn: linkedin.com/in/stevelopezenterprise
- GitHub: github.com/BigZeeke
- Role: Director, Client Communications & Analytics → Analytics Engineering