Skip to content

README.md

Latest commit

 

History

History
283 lines (205 loc) · 9.28 KB

README.md

File metadata and controls

283 lines (205 loc) · 9.28 KB
Raw

ProDataViz Logo

ProDataViz

Interactive analytics platform & SQL learning environment for French Master's graduate employment data (2010 – 2020).

Quick Start  French

Next.js 16 FastAPI SQLite Chart.js Monaco i18n MIT License

Why ProDataViz?

Most SQL learning tools use toy datasets. ProDataViz uses real open-data — 100 000+ records from data.gouv.fr covering salaries, employment rates, and career outcomes of French university graduates — to create an immersive, production-grade analytics + education platform.

📊 Analytics Suite

  • Dashboard — KPI cards, salary evolution charts, discipline breakdowns
  • Data Explorer — Paginated, multi-filter table over 100k+ records
  • Rankings — Top universities by salary & employment rate
  • Compare — Radar chart to benchmark up to 5 universities

🎓 SQL Learning

  • Learn SQL — 10 interactive lessons from SELECT to Window Functions
  • SQL Lab — Monaco editor, live schema browser, EXPLAIN plans, complexity scoring
  • 20 Challenges — Gamified progressive exercises with auto-grading & chart visualizations

🖥️ Screenshots

Dark glassmorphic UI — responsive, premium design with blur effects, gradient accents, and smooth animations.

Dashboard SQL Lab Challenges
Dashboard SQL Lab Coming soon

🏗️ Architecture

prodataviz/
├── backend/                 # FastAPI REST API
│   ├── app/
│   │   ├── models.py        # 7 SQLAlchemy models (3NF)
│   │   ├── crud.py          # Query layer
│   │   ├── schemas.py       # Pydantic validation
│   │   └── routers/         # academies, analytics, sql_lab, statistiques
│   └── scripts/seed.py      # CSV → SQLite ETL pipeline
├── frontend/                # Next.js 16 (App Router)
│   ├── app/[locale]/        # i18n-aware pages (fr / en)
│   │   ├── page.js          # Dashboard
│   │   ├── explorer/        # Data Explorer
│   │   ├── classements/     # Rankings
│   │   ├── comparer/        # University Comparator
│   │   ├── apprendre-sql/   # 10-lesson SQL course
│   │   ├── sql-lab/         # Full SQL editor
│   │   └── defis/           # 20 gamified challenges
│   ├── components/          # Shared UI components
│   ├── lib/api.js           # Backend API client
│   └── messages/            # i18n translation files
├── data/                    # Raw CSV + JSON dataset
├── docs/                    # DB architecture & SQL guide
└── start.sh                 # One-command launcher

Tech Stack

Layer Technology Purpose
Frontend Next.js 16, React 19 App Router, SSR, i18n routing
Editor Monaco Editor VS Code-grade SQL editing
Charts Chart.js + react-chartjs-2 Bar, Line, Radar, Doughnut visualizations
i18n next-intl Seamless French / English
Backend FastAPI, SQLAlchemy 2.0 Async REST API, ORM
Database SQLite (3NF, 7 tables) Normalized relational schema
Styling Custom CSS (Glassmorphism) Premium dark theme, fully responsive
Tooling uv, npm Fast dependency management

Database Schema (3NF)

academie ─┬─ etablissement ──┬── statistique ──┬── enquete
           │                  │                 │
           └──────────────────┘   discipline ───┘
                                       │
                                   domaine

           donnees_nationales (standalone aggregate table)

7 tables, fully normalized to Third Normal Form. See docs/architecture.md for the complete ERD and indexing strategy.

🚀 Quick Start

Prerequisites

Tool Version Install
Python 3.12+ python.org
uv latest curl -LsSf https://astral.sh/uv/install.sh | sh
Node.js 20+ nodejs.org

Option A: One-Command Launch

# Clone the repo
git clone https://github.com/toufik-ferhat/prodataviz.git
cd prodataviz

# Install & seed everything, then launch both servers
chmod +x start.sh && ./start.sh

Option B: Manual Setup

Step-by-step instructions

1. Backend

cd backend

# Install dependencies
uv sync

# Seed the SQLite database from CSV (~2s)
uv run scripts/seed.py

# Start API server on port 8000
uv run app/main.py

2. Frontend (new terminal)

cd frontend

# Install dependencies
npm install

# Start dev server on port 3000
npm run dev

Open http://localhost:3000 and start exploring!

🎯 Features in Detail

Interactive SQL Learning (10 Lessons)

A structured course from zero to advanced SQL, using the real ProDataViz dataset:

# Topic Key Concepts
1 SELECT Choosing columns, FROM, *
2 WHERE Filtering, comparison operators, LIKE, AND/OR
3 ORDER BY & LIMIT Sorting, pagination, ASC/DESC
4 Aggregates COUNT, SUM, AVG, MIN, MAX
5 GROUP BY Grouping, aggregation per category
6 HAVING Filtering groups post-aggregation
7 JOIN INNER JOIN, relating tables
8 Multi-table JOINs 3+ tables, complex relationships
9 Subqueries Nested SELECT, IN, correlated
10 Window Functions ROW_NUMBER, RANK, LAG, OVER

Each lesson includes theory, syntax reference, live interactive example, and a practice exercise with hint system and auto-verification.

SQL Challenges (20 Exercises)

Three difficulty tiers with automatic grading:

  • ⭐ Beginner (1–5) — SELECT, WHERE, ORDER BY, COUNT, MAX
  • ⭐⭐ Intermediate (6–10) — JOIN, GROUP BY, HAVING, CASE WHEN, subqueries
  • ⭐⭐⭐ Advanced (11–15) — RANK(), LAG(), ROW_NUMBER(), CTE, complex queries
  • 📊 Visualization (16–20) — Data viz challenges that render results as Bar, Line, Doughnut, and Multi-line charts

SQL Lab

A full-featured SQL workbench:

  • Monaco Editor with SQL syntax highlighting
  • Schema Browser — click to explore all 7 tables and columns
  • EXPLAIN Query Plan — see SQLite execution plans
  • Complexity Score — real-time analysis of query efficiency
  • Result Table — paginated results with execution time

🌐 Internationalization

Full French and English support powered by next-intl:

  • URL-based locale routing (/fr/..., /en/...)
  • All UI text externalized in JSON translation files
  • Language switcher in the sidebar
  • SQL lesson content available in both languages

📚 Documentation

Document Description
Database Architecture ERD, 3NF justification, indexing strategy
SQL Guide & Challenges All 20 challenges with solutions

🤝 Contributing

Contributions are welcome! Here's how:

  1. Fork the repository
  2. Create a feature branch (git checkout -b feature/amazing-feature)
  3. Commit your changes (git commit -m 'Add amazing feature')
  4. Push to the branch (git push origin feature/amazing-feature)
  5. Open a Pull Request

📄 License

This project is licensed under the MIT License — see the LICENSE file for details.

👥 Authors

Toufik FERHAT Asma DAGMOUNE

University project — Information Systems

Data source: data.gouv.fr — French government open data portal

Built with ❤️ using Next.js, FastAPI, Chart.js & Monaco Editor