Project Overview

I built this project to brush up on my SQL skills and get more comfortable working with real-world style datasets. It uses a PostgreSQL database containing historical Formula 1 race data from 1950 to 2022, which I expose through a lightweight API and query to produce simple driver performance summary tables.

The form on my website that lets you select a season and a metric, such as average finishing position or points per race, and it returns aggregated results for all drivers in that year. On the backend, I use SQL queries in PostgreSQL to group, filter, and compute these statistics efficiently.

The front end is a lightweight JavaScript interface that sends requests to an API endpoint and dynamically updates the table without reloading the page. The goal was to keep things simple and focus on solid database querying and clean data flow from database to UI.

Give it a Go!

Driver	Races	Value

Fetch Data from API and Populate the Table

async function load() {
  const season = document.getElementById("season").value;
  const metric = document.getElementById("metric").value;

  const res = await fetch(`/api/season-stats?season=${season}&metric=${metric}`);
  const data = await res.json();

  document.getElementById("table").innerHTML = data.map(row => `
    
      ${row.name}
      ${row.races}
      ${row.value}
    
  `).join('');
}

document.getElementById("season").addEventListener("change", load);
document.getElementById("metric").addEventListener("change", load);

load();
    

Query Handler and Data Analysis

app.get("/api/season-stats", async (req, res) => {
  const season = parseInt(req.query.season);
  const metric = req.query.metric;

  if (!season || !metric) {
    return res.status(400).json({ error: "season and metric required" });
  }

  try {
    let query = "";

    if (metric === "avg_position") {
      query = `
        SELECT
          r.season,
          d.name,
          COUNT(*) AS races,
          ROUND(AVG(res.position)::NUMERIC, 3) AS value
        FROM results res
        JOIN drivers d ON res.driver_id = d.driver_id
        JOIN races r ON res.race_id = r.race_id
        WHERE res.position IS NOT NULL
          AND r.season = $1
        GROUP BY r.season, d.name
        ORDER BY value ASC;
      `;
    }

    else if (metric === "points_per_race") {
      query = `
        SELECT
          r.season,
          d.name,
          COUNT(*) AS races,
          ROUND((SUM(res.points)::NUMERIC / COUNT(*)), 4) AS value
        FROM results res
        JOIN drivers d ON res.driver_id = d.driver_id
        JOIN races r ON res.race_id = r.race_id
        WHERE r.season = $1
        GROUP BY r.season, d.name
        ORDER BY value DESC;
      `;
    }

    else {
      return res.status(400).json({ error: "invalid metric" });
    }

    const result = await pool.query(query, [season]);
    res.json(result.rows);

  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "database error" });
  }
});