mercaridb-mysql-30days - GitLite

KO | EN

gitlite — search

#swift #animation #computer-vision #sql #llm #api #react #cross-platform #rust #artificial-intelligence

mercaridb-mysql-30days

(★ 9)

**A production-grade SQL learning project modeled on real e-commerce data scenarios** *Inspired by Mercari Japan — Japan's #1 C2C Marketplace*

파일 탐색기

ZIP 다운로드

Day 29: Full schema optimization for 10M users
Day 4: ORDER BY, LIMIT, OFFSET — pagination complete
day10_subqueries.sql
day11_exists_not_exists.sql
day12_union_intersect_except.sql
day13_string_date_functions.sql
day14_week2_practice.sql
day15_window_functions (4).sql
day16_lag_lead_ntile.sql
day17_case_when.sql
day18_ctes.sql
day1_setup.sql
day20_views.sql
day21_week3_practice.sql
day22_query_optimization.sql
day23_normalization.sql
day24_transactions_acid.sql
day25_stored_procedures.sql
day26_case_study.sql
day27_leetcode_part1.sql
day28_leetcode_part2.sql
day2_select_where.sql
day30_final_bi_report.sql
day3_and_or_in_between.sql
day5_aggregate_functions.sql
day6_groupby_having.sql
day7_practice.sql
day8_joins.sql
day9_right_self_multijoin.sql
README.md

# CDN으로 사용하기

jsDelivr

jsDelivr는 공개 GitHub 리포지토리를 별도 설정 없이 CDN으로 즉시 서빙합니다. 버전과 파일을 고르면 웹페이지에 바로 붙일 수 있는 링크와 예시 코드가 만들어집니다.

버전

파일

링크

예시

명령어 용어집

이 문서에서 사용된 명령어를 모아봤습니다. 낯선 명령어가 있다면 펼쳐서 확인해보세요.

🔍

`git clone`

설명 보기 ▼

Clone an existing repository.

git clone {{remote_repository_location}} {{path/to/directory}}

Clone an existing repository into a new directory (the default directory is the repository name):

git clone --recursive {{remote_repository_location}}

Clone an existing repository and its submodules:

git clone {{[-n|--no-checkout]}} {{remote_repository_location}}

Clone only the `.git` directory of an existing repository:

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

**A production-grade SQL learning project modeled on real e-commerce data scenarios**  
*Inspired by Mercari Japan — Japan's #1 C2C Marketplace*

[![SQL](https://img.shields.io/badge/SQL-MySQL%20%2F%20PostgreSQL-336791?logo=postgresql&logoColor=white)](https://www.mysql.com/)
[![Challenge](https://img.shields.io/badge/Challenge-30%20Days-ff6b6b?style=flat)](https://github.com/Prashant-4527/mercaridb-mysql-30days)
[![Status](https://img.shields.io/badge/Status-✅%20Complete-brightgreen?style=flat)](https://github.com/Prashant-4527/mercaridb-mysql-30days)
[![Inspiration](https://img.shields.io/badge/Inspired%20by-Mercari%20Japan-E40046?logo=data:image/png;base64,)](https://mercari.com)
[![License](https://img.shields.io/badge/License-MIT-green.svg)](https://github.com/Prashant-4527/mercaridb-mysql-30days/blob/main/LICENSE)

> *"The best way to learn SQL is to solve real problems."*  
> **メルカリで日本を目指す！** — Aiming for Mercari Japan 🇯🇵

---

## 📖 Table of Contents

- [About the Project](#-about-the-project)
- [Why Mercari?](#-why-mercari)
- [Database Schema](#️-database-schema)
- [30-Day Curriculum](#-30-day-curriculum)
- [Sample Queries](#-sample-queries)
- [Getting Started](#-getting-started)
- [Progress Tracker](#-progress-tracker)
- [Skills Gained](#-skills-gained)
- [Roadmap](#️-roadmap)
- [Connect](#-connect)

---

## 🎯 About the Project

**MercariDB** is not a toy dataset. It is a structured 30-day deep-dive into SQL using a purpose-built, realistic simulation of Mercari's C2C marketplace database — complete with users, product listings, transactions, reviews, categories, and shipping logistics.

Every query in this project answers a **real business question** — the kind a Data Engineer, Data Analyst, or Backend Engineer would face working at a tech company like Mercari Japan. No abstract exercises. No "SELECT * FROM students" fluff.

**The result:** 30 progressively challenging SQL files covering everything from schema design and basic filtering all the way to window functions, CTEs, cohort analysis, and a full end-to-end BI seller dashboard.

---

## 🏢 Why Mercari?

[Mercari](https://mercari.com) is Japan's largest C2C marketplace with **20M+ monthly active users**, operating across Japan, the US, and India. As a target company for an **AI Engineering career in Japan**, studying its data model delivers:

- **Real context** — every query solves a plausible business problem, not an abstract exercise
- **E-commerce depth** — pricing, shipping, fraud detection, cohort analysis, and recommendation logic
- **Interview readiness** — the exact categories of SQL questions asked in data/backend interviews at top Japanese tech companies
- **Domain fluency** — understanding the data pipelines behind a world-class marketplace product

---

## 🗄️ Database Schema

The `mercaridb` database consists of six interconnected tables that mirror the core domain of a C2C marketplace:

```
mercaridb
│
├── users           — Registered accounts (user_id, username, email, country, age, created_at, verified)
├── products        — Active listings  (product_id, seller_id, title, category_id, price, condition, status)
├── transactions    — Purchase records (transaction_id, buyer_id, seller_id, product_id, amount, purchased_at)
├── categories      — Hierarchical taxonomy (category_id, name, parent_id)
├── reviews         — Seller ratings   (review_id, reviewer_id, seller_id, rating, comment, created_at)
└── shipping        — Delivery details (shipping_id, transaction_id, carrier, status, shipped_at, delivered_at)
```

### Entity Relationship Overview

```
users ──< products (seller_id)
users ──< transactions (buyer_id, seller_id)
products ──< transactions (product_id)
categories ──< products (category_id)
transactions ──< shipping (transaction_id)
transactions ──< reviews (seller_id ← reviewer_id)
```

All tables are created with proper constraints — `NOT NULL`, `UNIQUE`, `DEFAULT`, `AUTO_INCREMENT`, `FOREIGN KEY` — to enforce relational integrity from Day 1.

---

## 📅 30-Day Curriculum

### 🟢 Week 1 — Foundations (Days 1–7)
> *"Know the tools before you build the house."*

| Day | File | Topic | Business Question |
|-----|------|--------|-------------------|
| 01 | `day1_setup.sql` | Schema design, CREATE TABLE, INSERT | *"Bootstrap the mercaridb database with realistic seed data"* |
| 02 | `day2_select_where.sql` | SELECT, WHERE, column aliases | *"Show all active listings under ¥5,000 in the Electronics category"* |
| 03 | `day3_and_or_in_between.sql` | AND, OR, IN, BETWEEN, NOT | *"Find listings priced between ¥1,000–¥10,000 from verified sellers"* |
| 04 | `day4_orderby_limit_offset.sql` | ORDER BY, LIMIT, OFFSET | *"Build paginated results: top 10 cheapest listings per page"* |
| 05 | `day5_aggregate_functions.sql` | COUNT, SUM, AVG, MIN, MAX | *"What is the average selling price per category?"* |
| 06 | `day6_groupby_having.sql` | GROUP BY, HAVING | *"Which sellers have completed more than 10 transactions?"* |
| 07 | `day7_practice.sql` | Week 1 combined challenge | *Mini business report: top categories, active sellers, revenue summary* |

---

### 🟡 Week 2 — Intermediate (Days 8–14)
> *"Relational thinking is the core of SQL mastery."*

| Day | File | Topic | Business Question |
|-----|------|--------|-------------------|
| 08 | `day8_joins.sql` | INNER JOIN, LEFT JOIN | *"Show every transaction with full buyer and seller details"* |
| 09 | `day9_right_self_multijoin.sql` | RIGHT JOIN, SELF JOIN, multi-table JOINs | *"Full transaction report: product + buyer + seller + shipping in one query"* |
| 10 | `day10_subqueries.sql` | Scalar & correlated subqueries | *"Find all products priced above their category's average"* |
| 11 | `day11_exists_not_exists.sql` | EXISTS, NOT EXISTS | *"Find users who have never made a single purchase"* |
| 12 | `day12_union_intersect_except.sql` | UNION, INTERSECT, EXCEPT | *"Combine buyer and seller activity into a unified user feed"* |
| 13 | `day13_string_date_functions.sql` | String & date functions | *"Parse transaction timestamps, format seller names, extract month/year"* |
| 14 | `day14_week2_practice.sql` | Week 2 combined challenge | *Business report: join-heavy seller performance analysis* |

---

### 🟠 Week 3 — Advanced (Days 15–21)
> *"Window functions are what separate good SQL from great SQL."*

| Day | File | Topic | Business Question |
|-----|------|--------|-------------------|
| 15 | `day15_window_functions.sql` | ROW_NUMBER, RANK, DENSE_RANK | *"Rank sellers by revenue within each category"* |
| 16 | `day16_lag_lead_ntile.sql` | LAG, LEAD, NTILE | *"Compare each user's latest purchase to their previous one"* |
| 17 | `day17_case_when.sql` | CASE WHEN, conditional logic | *"Classify products as Budget / Mid-range / Premium"* |
| 18 | `day18_ctes.sql` | CTEs (WITH clause), recursive CTEs | *"Identify top 10 sellers by revenue this quarter using readable CTEs"* |
| 19 | `day19_indexes_explain.sql` | Indexes, EXPLAIN, query planning | *"Why is this query slow? Profile it and fix it."* |
| 20 | `day20_views.sql` | Views, updatable views | *"Create a reusable `active_sellers_v` view for BI dashboards"* |
| 21 | `day21_week3_practice.sql` | Week 3 combined challenge | *Seller performance dashboard using window functions + CTEs* |

---

### 🔴 Week 4 — Real-World Scenarios (Days 22–30)
> *"This is where SQL meets the business. These are the queries that get you hired."*

| Day | File | Topic | Business Question |
|-----|------|--------|-------------------|
| 22 | `day22_query_optimization.sql` | Query optimization, indexes at scale | *"Optimize for 10M rows — before and after EXPLAIN ANALYZE"* |
| 23 | `day23_normalization.sql` | 1NF → 3NF, schema refactoring | *"Redesign the categories table to support infinite nesting"* |
| 24 | `day24_transactions_acid.sql` | Transactions, ACID properties, rollback | *"Safely process a purchase: deduct stock, log transaction, notify"* |
| 25 | `day25_stored_procedures.sql` | Stored procedures, parameterized queries | *"Automate monthly seller payout calculation as a stored procedure"* |
| 26 | `day26_case_study.sql` | End-to-end case study | *Full cohort + funnel + fraud detection analysis in one file* |
| 27 | `day27_leetcode_part1.sql` | LeetCode SQL classics, Part 1 | *Nth highest salary, consecutive numbers, duplicate emails* |
| 28 | `day28_leetcode_part2.sql` | LeetCode SQL classics, Part 2 | *Moving average, pivot, median, game analytics, advanced windows* |
| 29 | `day29_schema_optimization.sql` | Full schema optimization | *"Redesign mercaridb for 10M users: partitioning, indexing strategy"* |
| 30 | `day30_final_bi_report.sql` | ✅ Final project | *End-to-end Business Intelligence report — the complete seller dashboard* |

---

## 💡 Sample Queries

### Day 30 — Seller Scorecard with Revenue Rank

```sql
WITH seller_stats AS (
    SELECT
        t.seller_id,
        u.username                       AS seller_name,
        COUNT(t.transaction_id)          AS total_sales,
        SUM(t.amount)                    AS gross_revenue,
        ROUND(AVG(t.amount), 0)          AS avg_order_value
    FROM transactions t
    INNER JOIN users u ON t.seller_id = u.user_id
    GROUP BY t.seller_id, u.username
)
SELECT
    seller_name,
    total_sales,
    CONCAT('¥', FORMAT(gross_revenue, 0))      AS gross_revenue,
    RANK() OVER (ORDER BY gross_revenue DESC)  AS revenue_rank
FROM seller_stats
ORDER BY revenue_rank;
```

---

### Day 15 — Ranking Sellers by Revenue per Category

```sql
WITH seller_revenue AS (
    SELECT
        p.category_id,
        c.name        AS category,
        t.seller_id,
        u.username    AS seller_name,
        SUM(t.amount) AS total_revenue,
        COUNT(t.transaction_id) AS total_sales
    FROM transactions t
    JOIN products   p ON t.product_id  = p.product_id
    JOIN categories c ON p.category_id = c.category_id
    JOIN users      u ON t.seller_id   = u.user_id
    GROUP BY p.category_id, c.name, t.seller_id, u.username
)
SELECT
    category,
    seller_name,
    total_revenue,
    total_sales,
    RANK() OVER (PARTITION BY category_id ORDER BY total_revenue DESC) AS rank_in_category
FROM seller_revenue
ORDER BY category, rank_in_category;
```

---

### Day 24 — ACID-Compliant Transaction Processing

```sql
START TRANSACTION;

UPDATE products
    SET status = 'sold'
    WHERE product_id = 42 AND status = 'active';

INSERT INTO transactions (buyer_id, seller_id, product_id, amount, purchased_at)
    VALUES (7, 3, 42, 4800.00, NOW());

INSERT INTO shipping (transaction_id, carrier, status)
    VALUES (LAST_INSERT_ID(), 'Yamato Transport', 'pending');

COMMIT;
-- On any error: ROLLBACK; — atomicity guaranteed
```

---

### Day 18 — CTE-Based Seller Leaderboard

```sql
WITH quarterly_sales AS (
    SELECT seller_id, SUM(amount) AS revenue, COUNT(*) AS sales_count
    FROM transactions
    WHERE purchased_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
    GROUP BY seller_id
),
ranked_sellers AS (
    SELECT
        u.username,
        u.country,
        qs.revenue,
        qs.sales_count,
        DENSE_RANK() OVER (ORDER BY qs.revenue DESC) AS leaderboard_rank
    FROM quarterly_sales qs
    JOIN users u ON qs.seller_id = u.user_id
)
SELECT * FROM ranked_sellers WHERE leaderboard_rank <= 10;
```

---

## 🚀 Getting Started

### Prerequisites

- MySQL 8.0+ (or PostgreSQL 13+ — most queries are standard SQL)
- MySQL Workbench, DBeaver, or any SQL client
- Git

### Setup

```bash
# 1. Clone the repository
git clone https://github.com/Prashant-4527/mercaridb-sql-30days.git
cd mercaridb-sql-30days

# 2. Open your SQL client and run the setup file first
source day1_setup.sql

# 3. Run each day's file in order
source day2_select_where.sql
source day3_and_or_in_between.sql
# ... continue through day30
```

### Recommended Learning Flow

```
Day 1       → Run setup, verify tables exist
Day 2–7     → Core SELECT skills, run each file, study the comments
Day 8–14    → JOINs and subqueries — pause here and write your own queries
Day 15–21   → Window functions — these WILL trip you up. Go slow.
Day 22–28   → Real-world scenarios — treat each file as a mini case study
Day 29      → Schema optimization — read EXPLAIN output carefully
Day 30      → Final project — close the laptop, write it from scratch
```

> 💡 **Tip:** Don't just run the files — read every comment, modify the queries, and try to predict the output before executing. That's how the learning sticks.

---

## 📈 Progress Tracker

| Week | Theme | Days | Status |
|------|-------|------|--------|
| Week 1 | Foundations | Days 1–7 | ✅ Complete |
| Week 2 | Intermediate | Days 8–14 | ✅ Complete |
| Week 3 | Advanced | Days 15–21 | ✅ Complete |
| Week 4 | Real-World Scenarios | Days 22–30 | ✅ Complete |

**Overall: `30 / 30 days` — 🎉 100% COMPLETE**

---

## 🧠 Skills Gained

By the end of this challenge, the following SQL competencies are demonstrated:

**Core SQL**
- Schema design, DDL (`CREATE`, `DROP`, `ALTER`), DML (`INSERT`, `UPDATE`, `DELETE`)
- Filtering with `WHERE`, `AND`/`OR`, `IN`, `BETWEEN`, `LIKE`, `IS NULL`
- Sorting and pagination with `ORDER BY`, `LIMIT`, `OFFSET`
- Aggregation: `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`
- Grouping with `GROUP BY` + filtering groups with `HAVING`

**Relational SQL**
- All JOIN types: `INNER`, `LEFT`, `RIGHT`, `FULL OUTER`, `SELF`, multi-table
- Subqueries (scalar, correlated, inline views)
- `EXISTS` / `NOT EXISTS` for semi-join patterns
- Set operations: `UNION`, `INTERSECT`, `EXCEPT`

**Advanced SQL**
- Window functions: `ROW_NUMBER`, `RANK`, `DENSE_RANK`, `NTILE`, `PERCENT_RANK`
- Offset functions: `LAG`, `LEAD`
- Running totals and moving averages with `OVER (PARTITION BY ... ORDER BY ...)`
- CTEs (`WITH` clause) for readable, modular queries
- `CASE WHEN` for conditional transformations

**Production SQL**
- Query optimization with `EXPLAIN` / `EXPLAIN ANALYZE`
- Index strategy: B-tree, composite, covering indexes
- Database normalization: 1NF → 2NF → 3NF
- ACID transactions with `START TRANSACTION`, `COMMIT`, `ROLLBACK`
- Stored procedures and parameterized logic
- Views for abstraction and reusability
- Partitioning strategy for 10M+ row tables

**Business Analytics**
- Cohort analysis (user retention by signup month)
- Funnel analysis (list → offer → transaction conversion)
- Fraud detection (anomaly flagging via statistical thresholds)
- Month-over-month revenue growth with LAG
- Seller payout readiness reports
- End-to-end BI dashboard synthesis
- LeetCode SQL problem-solving patterns

---

## 🗺️ Roadmap

- [x] Week 1: Core SQL foundations
- [x] Week 2: JOINs, subqueries, set operations
- [x] Week 3: Window functions, CTEs, views
- [x] Week 4: Optimization, transactions, stored procedures
- [x] Day 30: Final BI report — the complete seller analytics dashboard
- [ ] Add PostgreSQL variants for all queries
- [ ] Add `EXPLAIN` outputs as markdown tables for optimization days
- [ ] Extend schema: add `offers` and `messages` tables (Days 31–35?)
- [ ] Export final BI report as a PDF summary

---

## 🛠️ Tech Stack

| Tool | Purpose |
|------|---------|
| **MySQL 8.0** | Primary database engine |
| **MySQL Workbench** | Query editor and schema visualizer |
| **SQL (standard)** | Language — queries are largely portable to PostgreSQL |
| **Git + GitHub** | Version control and daily commit discipline |

---

## 📬 Connect

- **GitHub:** [@Prashant-4527](https://github.com/Prashant-4527)
- **Location:** Jaipur, Rajasthan, India 🇮🇳
- **Career Target:** AI Engineer @ Mercari Japan 🇯🇵 by 2028

---

## 📄 License

This project is open source under the [MIT License](https://github.com/Prashant-4527/mercaridb-mysql-30days/blob/main/LICENSE). Feel free to fork, adapt, and build your own domain-specific SQL challenge on top of it.

---

*Built with curiosity, consistency, and coffee. One query at a time.*  
**30日間達成！— 30 days done. メルカリ、ここに向かっている。🇯🇵**

**⭐ Star this repo if it helped you learn SQL through real-world problems.**