A production-quality SQL analytics project built on the real Mercari Price Suggestion dataset. From a messy real-world CSV to validated business intelligence — using pure SQL.
Learning repo → mercaridb-mysql-30days This repo → The capstone. Real data. Real bugs found and fixed. Real, measured insights.
This is not a tutorial follow-along.
MercariDB Analytics normalizes and analyzes 50,000 real listings from the Kaggle Mercari Price Suggestion Challenge dataset — the actual product catalogue of Japan's largest C2C marketplace, not a simulation. Every query starts with a business question. Every query in this repo was executed against a fully loaded 50,000-row MySQL database before being committed — not just checked for syntax, but run, and its output checked for whether it made sense.
Built after 30 days of structured SQL study. Written from scratch.
A note on project evolution: this project originally started around a fictional users/orders/buyers marketplace schema. Partway through, it pivoted to real Mercari listing data instead — real data is worth more on a portfolio than simulated data, even at the cost of losing some concepts (buyer segmentation, referral chains, cross-country flow) that need transaction data this dataset doesn't have. Sections below are honest about which original business questions could be answered with real data and which were reworked into their closest meaningful equivalent — see
insights/KEY_FINDINGS.md§7 for the specific bugs that turned up during that process.
erDiagram
CATEGORIES ||--o{ LISTINGS : categorizes
BRANDS ||--o{ LISTINGS : brands
ITEM_CONDITIONS ||--|{ LISTINGS : grades
LISTINGS {
int listing_id PK
varchar title
tinyint condition_id FK
int category_id FK "nullable"
int brand_id FK "nullable"
decimal price
tinyint shipping_paid_by
text description
}
CATEGORIES {
int category_id PK
varchar raw_category
varchar main_category
varchar sub_category "nullable"
varchar sub_sub_category "nullable"
}
BRANDS {
int brand_id PK
varchar brand_name
}
ITEM_CONDITIONS {
tinyint condition_id PK
varchar condition_name
}
raw_listings (not pictured) is a fifth table — an unindexed staging table that mirrors the source CSV column-for-column. Nothing queries it directly; it exists purely so LOAD DATA INFILE has a dumb landing zone before schema/02_seed_data.sql normalizes it out into the four tables above.
Data dictionary:
| Table | Column | Type | Notes |
|---|---|---|---|
listings |
listing_id |
INT PK |
Same as the source dataset's train_id |
title |
VARCHAR(500) |
||
condition_id |
TINYINT FK |
1 (New) – 5 (Poor) | |
category_id |
INT FK, NULL |
NULL for the 0.47% of listings with no category | |
brand_id |
INT FK, NULL |
NULL for the 43.2% of listings with no brand | |
price |
DECIMAL(10,2) |
Never FLOAT for money |
|
shipping_paid_by |
TINYINT |
1 = seller pays, 0 = buyer pays | |
description |
TEXT |
Mercari auto-fills "No description yet" when a seller skips this field |
|
categories |
raw_category |
VARCHAR, UNIQUE |
Original slash-delimited string, e.g. Women/Tops/T-shirts |
main_category / sub_category / sub_sub_category |
VARCHAR, NULL |
Split from raw_category via SUBSTRING_INDEX; deeper levels NULL where the source didn't have them |
|
brands |
brand_name |
VARCHAR, UNIQUE |
|
item_conditions |
condition_name |
VARCHAR |
New / Like New / Good / Fair / Poor |
Design decisions:
DECIMAL(10,2)for all monetary values — neverFLOATfor moneyNOT NULLon fields that are always present in the source;NULLallowed only where the real data is genuinely missing (category, brand) — enforcingNOT NULLthere would have meant inventing valuesFOREIGN KEYconstraints — referential integrity guaranteed- A composite index,
idx_category_price (category_id, price), added after profiling — not a guess; seeadvanced/query_optimization.sqlfor the measured ~230x speedup
| Table | Rows | Description |
|---|---|---|
raw_listings |
50,000 | Unmodified staging copy of the source CSV |
listings |
50,000 | Normalized fact table |
brands |
1,534 | Distinct brand names found in the data |
categories |
881 | Distinct raw category strings, split into up to 3 levels |
Source: Kaggle — Mercari Price Suggestion Challenge (train.tsv, re-exported here as comma-delimited mercari_sample.csv). Requires a free Kaggle account to download. Not committed to this repo — see How to Run below.
Markets covered (main_category): Women, Beauty, Kids, Electronics, Men, Home, Vintage & Collectibles, Other, Handmade, Sports & Outdoors. Condition scale: New, Like New, Good, Fair, Poor.
"How big is our platform? What are the core KPIs?"
| # | Question |
|---|---|
| 1 | Total listings, brands, categories, and GMV in one dashboard query |
| 2 | Branded vs unbranded listing split, with average price by type |
| 3 | Condition distribution and its relationship to price |
| 4 | Market coverage — listings and revenue share by main_category |
| 5 | Shipping model split — who pays, and how it varies |
"Which brands move volume? Which command the highest prices?"
| # | Question |
|---|---|
| 1 | Top 10 brands by listing count |
| 2 | Branded vs unbranded price premium |
| 3 | Top brand per category |
| 4 | Highest average-price brands (minimum listing threshold) |
| 5 | Brand diversity — categories spanned per brand |
"Where is the platform's revenue actually coming from?"
| # | Question |
|---|---|
| 1 | Top 15 sub-categories by GMV |
| 2 | Market classification — Core / Growing / Niche |
| 3 | Catalogue depth per market |
| 4 | Category × condition pivot |
| 5 | Revenue concentration — top 3 markets |
"What's overpriced? What's underpriced? What does condition actually cost?"
| # | Question |
|---|---|
| 1 | Price tier distribution — Budget / Mid-range / Premium |
| 2 | Condition price premium, indexed to "Good" |
| 3 | Top 3 listings per category by price (RANK per partition) |
| 4 | Listings priced above their own category average |
| 5 | Pricing data-quality outliers ($0 listings, extreme highs) |
"Does effort on a listing actually pay off?"
| # | Question |
|---|---|
| 1 | Shipping model by category |
| 2 | Does skipping the description cost the seller money? |
| 3 | Listing completeness score vs price |
| 4 | Title length vs price |
| 5 | Categories with the biggest "missing description" opportunity |
"What should we fix? Where should we invest?"
| # | Question |
|---|---|
| 1 | Brand whitespace — high-value categories with low brand penetration |
| 2 | Niche categories punching above their weight |
| 3 | Catalogue data-quality backlog |
| 4 | Condition-mix risk by category |
| 5 | Full opportunity summary with recommended actions |
Full write-up with every number sourced from an actual query run: insights/KEY_FINDINGS.md
Branding is worth 51.2%. Branded listings average $31.22 vs $20.64 unbranded — the single largest price lever in the dataset.
Electronics is the brand-whitespace opportunity. Highest average price of any market ($34.93) but 51.8% unbranded, well above the platform's 43.2% average.
Completeness beats almost everything else. Listings with both a brand and a real description average $31.59 vs $18.89 for listings with neither — a 67% gap that costs sellers nothing to close.
One index, 230x. A composite index on (category_id, price) took a representative category-browse query from 188ms to 0.8ms, measured with EXPLAIN ANALYZE — see advanced/query_optimization.sql.
Three real bugs were found by actually running this code, not by reading it — a LOAD DATA escaping issue that silently dropped 7 rows, a ROW_COUNT() timing bug, and a self-referencing UPDATE MySQL rejected outright. Details in KEY_FINDINGS.md §7.
Foundations
SELECT,WHERE,ORDER BY,LIMITGROUP BY+HAVING- Aggregate functions:
COUNT,SUM,AVG,MIN,MAX CASE WHENfor conditional logic and classification
Joins + Subqueries
INNER JOIN,LEFT JOIN, joins viaUSING, 3-table joins- Scalar subqueries, correlated subqueries, derived tables
UNION ALLfor set-based opportunity summaries
Advanced SQL
- Window functions:
ROW_NUMBER,RANK,NTILE - Offset functions:
LAG,LEAD - Running totals + moving averages with
OVER (ROWS BETWEEN ...) - CTEs (
WITHclause) — multi-step analytical pipelines - Recursive CTEs — generating price-bucket boundaries on the fly
Production Techniques
EXPLAIN/EXPLAIN ANALYZE+ measured index optimization (not guessed)VIEWS— reusable analytical layersSTORED PROCEDURES— parameterized business logicTRANSACTIONS+ ACID properties, withEXIT HANDLER FOR SQLEXCEPTIONNULLIF()+COALESCE()— NULL-safe calculations and fallback logicDECIMALoverFLOATfor monetary accuracy- Real-world CSV loading:
LOAD DATA LOCAL INFILEwithESCAPED BY,ENCLOSED BY, and line-terminator handling for messy free-text fields
CREATE OR REPLACE VIEW brand_scorecard AS
SELECT
b.brand_id,
b.brand_name,
COUNT(l.listing_id) AS total_listings,
ROUND(AVG(l.price), 2) AS avg_price,
ROUND(SUM(l.price), 2) AS total_catalogue_value,
COUNT(DISTINCT c.main_category) AS categories_spanned,
CASE
WHEN SUM(l.price) >= 20000 THEN 'Diamond'
WHEN SUM(l.price) >= 5000 THEN 'Gold'
WHEN SUM(l.price) >= 1000 THEN 'Silver'
ELSE 'Bronze'
END AS brand_tier,
RANK() OVER (ORDER BY SUM(l.price) DESC) AS value_rank
FROM brands b
JOIN listings l ON l.brand_id = b.brand_id
LEFT JOIN categories c ON l.category_id = c.category_id
GROUP BY b.brand_id, b.brand_name;WITH RECURSIVE price_buckets AS (
SELECT 0 AS bucket_start, 25 AS bucket_end
UNION ALL
SELECT bucket_end, bucket_end + 25
FROM price_buckets
WHERE bucket_end < (SELECT CEILING(MAX(price) / 25) * 25 FROM listings)
)
SELECT
CONCAT('$', pb.bucket_start, ' - $', pb.bucket_end) AS price_range,
COUNT(l.listing_id) AS listing_count
FROM price_buckets pb
LEFT JOIN listings l
ON l.price >= pb.bucket_start AND l.price < pb.bucket_end
GROUP BY pb.bucket_start, pb.bucket_end
ORDER BY pb.bucket_start;CREATE PROCEDURE reprice_zero_price_listings()
BEGIN
DECLARE v_updated_rows INT DEFAULT 0;
DECLARE v_global_avg DECIMAL(10,2);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
SELECT ROUND(AVG(price), 2) INTO v_global_avg FROM listings WHERE price > 0;
UPDATE listings l
LEFT JOIN (
SELECT category_id, AVG(price) AS cat_avg_price
FROM listings WHERE price > 0 GROUP BY category_id
) avgs ON l.category_id = avgs.category_id
SET l.price = ROUND(COALESCE(avgs.cat_avg_price, v_global_avg), 2)
WHERE l.price = 0;
SET v_updated_rows = ROW_COUNT(); -- captured before COMMIT — see KEY_FINDINGS.md §7
COMMIT;
SELECT v_updated_rows AS listings_repriced;
END;# 1. Clone the repo
git clone https://github.com/Prashant-4527/mercaridb-sql.git
cd mercaridb-sql
# 2. Get the data (not included in this repo — see Dataset Overview above)
# Download from Kaggle and save as mercari_sample.csv, OR bring any CSV
# with the same 8 columns as raw_listings in schema/01_schema.sql.
# 3. Enable LOCAL INFILE (needed once per session; see schema/02_seed_data.sql
# for why the default MySQL escape settings will silently drop rows)
mysql --local-infile=1 -u youruser -p
# 4. Run schema, then seed data
# In your MySQL client:
mysql> SOURCE schema/01_schema.sql;
mysql> SOURCE schema/02_seed_data.sql; -- edit the file path on line ~30 first
# 5. Run any analysis file
mysql> SOURCE analysis/01_platform_overview.sql;
mysql> SOURCE views/dashboard_views.sql;
mysql> SOURCE advanced/stored_procedures.sql;
# ... etc.Requirements: MySQL 8.0+ (window functions, WITH RECURSIVE, and EXPLAIN ANALYZE all need 8.0+) · MySQL Workbench (optional) · Git
mercaridb-sql/
│
├── README.md
│
├── schema/
│ ├── 01_schema.sql ← CREATE TABLE statements + indexes
│ └── 02_seed_data.sql ← LOAD DATA + normalization INSERTs
│
├── analysis/
│ ├── 01_platform_overview.sql
│ ├── 02_brand_intelligence.sql
│ ├── 03_category_market_analysis.sql
│ ├── 04_pricing_condition_intelligence.sql
│ ├── 05_shipping_and_listing_quality.sql
│ └── 06_growth_opportunities.sql
│
├── views/
│ └── dashboard_views.sql ← brand_scorecard, category_market_dashboard, listing_quality_flags
│
├── advanced/
│ ├── window_functions.sql
│ ├── cte_pipelines.sql ← includes a recursive CTE
│ ├── stored_procedures.sql ← includes a transaction/ACID demo
│ └── query_optimization.sql ← EXPLAIN ANALYZE + measured indexing
│
└── insights/
└── KEY_FINDINGS.md ← every number sourced from a real query run
- No buyer, seller, order, or country data. The real Mercari listings dataset only covers the catalogue side — who's browsing and buying isn't in it. Buyer segmentation, referral-chain analysis, and cross-country transaction flow (all present in an earlier draft of this project) would need real transaction data this dataset doesn't have, and weren't faked in to keep the count up.
item_descriptionfree text is under-used. AFULLTEXTindex and real search/keyword-mining pass would be the natural next step — not added by default since it roughly doubles table size on disk for a feature no current query needs (see the trade-off note inadvanced/query_optimization.sql).- Single-market snapshot, not a time series. The dataset is a point-in-time catalogue export — trend analysis (growth month-over-month, seasonality) isn't possible without a
listed_datecolumn, which the source data doesn't include.
| Project | Description |
|---|---|
| mercaridb-mysql-30days | 30-day structured SQL learning journey |
| Mercari-analytics-report | Earlier analytics report (Week 1-2 level) |
| EduTrack-oop-numpy | Python OOP + NumPy analytics system |
Prashant — BCA Student @ Maharaja College Jaipur Self-directing a multi-year curriculum toward an AI Engineering role at Mercari Japan by 2028.
Stack: Python · MySQL · NumPy · Pandas (in progress) · DSA (daily) Languages: English · Hindi · Japanese (N4 → N3) · German (A2) Target: METI IPA Internship 2027 → Mercari Japan 2028 🇯🇵
Built from scratch. Every query run against real data before being committed. No shortcuts. メルカリで日本を目指す！