Les requêtes SQL indispensables pour un Data Analyst

SQL est le langage fondamental de tout Data Analyst. Maîtriser les bonnes requêtes permet de répondre rapidement aux questions métier, nettoyer les données et alimenter les dashboards. Voici les patterns SQL que j'utilise au quotidien.

Agrégations essentielles

Les fonctions d'agrégation sont la base :

SELECT
  categorie,
  COUNT(*) AS nb_lignes,
  SUM(montant) AS total,
  AVG(montant) AS moyenne,
  MAX(montant) AS maximum
FROM transactions
GROUP BY categorie
HAVING SUM(montant) > 10000
ORDER BY total DESC;

Les JOINs pour croiser les tables

Les jointures permettent de croiser plusieurs tables :

SELECT
  c.nom_client,
  COUNT(v.id) AS nb_achats,
  SUM(v.montant) AS ca_total
FROM clients c
LEFT JOIN ventes v ON c.id = v.client_id
GROUP BY c.nom_client
ORDER BY ca_total DESC NULLS LAST;

Fonctions fenêtres (Window Functions)

Les window functions sont très puissantes pour les analyses temporelles :

SELECT
  date,
  montant,
  SUM(montant) OVER (ORDER BY date) AS cumul,
  AVG(montant) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moy_mobile_7j
FROM ventes;

CTEs pour des requêtes lisibles

Les Common Table Expressions rendent les requêtes complexes lisibles :

WITH clients_actifs AS (
  SELECT client_id, SUM(montant) as ca
  FROM ventes
  WHERE date >= DATEADD(month,-3,GETDATE())
  GROUP BY client_id
)
SELECT c.nom, ca.ca
FROM clients c
JOIN clients_actifs ca ON c.id = ca.client_id;

Détecter les anomalies et doublons

SQL est très efficace pour détecter les problèmes de qualité :

-- Doublons
SELECT id, COUNT(*) FROM table
GROUP BY id HAVING COUNT(*) > 1;

-- Valeurs nulles
SELECT COUNT(*) - COUNT(colonne) AS nb_nulls
FROM table;

Conclusion

Ces patterns SQL couvrent l'essentiel des besoins quotidiens d'un Data Analyst. La maîtrise de SQL — en particulier les window functions et les CTEs — permet de répondre à des questions métier complexes avec efficacité et précision.

SQL is the foundational language for every Data Analyst. Mastering the right queries lets you answer business questions fast, clean data and feed dashboards. Here are the SQL patterns I use every day.

Essential aggregations

Aggregation functions are the foundation:

SELECT
  category,
  COUNT(*) AS nb_rows,
  SUM(amount) AS total,
  AVG(amount) AS average,
  MAX(amount) AS maximum
FROM transactions
GROUP BY category
HAVING SUM(amount) > 10000
ORDER BY total DESC;

JOINs to combine tables

Joins let you combine multiple tables:

SELECT
  c.customer_name,
  COUNT(s.id) AS nb_purchases,
  SUM(s.amount) AS total_revenue
FROM customers c
LEFT JOIN sales s ON c.id = s.customer_id
GROUP BY c.customer_name
ORDER BY total_revenue DESC NULLS LAST;

Window functions

Window functions are powerful for time-series analysis:

SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS running_total,
  AVG(amount) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_avg_7d
FROM sales;

CTEs for readable queries

Common Table Expressions make complex queries readable:

WITH active_customers AS (
  SELECT customer_id, SUM(amount) as revenue
  FROM sales
  WHERE date >= DATEADD(month,-3,GETDATE())
  GROUP BY customer_id
)
SELECT c.name, ac.revenue
FROM customers c
JOIN active_customers ac ON c.id = ac.customer_id;

Detecting anomalies and duplicates

SQL is very effective for quality checks:

-- Duplicates
SELECT id, COUNT(*) FROM table
GROUP BY id HAVING COUNT(*) > 1;

-- Null values
SELECT COUNT(*) - COUNT(column) AS nb_nulls
FROM table;

Conclusion

These SQL patterns cover the day-to-day needs of a Data Analyst. Mastering SQL — especially window functions and CTEs — lets you answer complex business questions efficiently and precisely.