Chapitre 8.3 — Stats & records : le SQL qui fait les écrans

Où on en est : les sessions s’enregistrent. On écrit maintenant les requêtes qui font vivre l’onglet Historique et l’écran résultat — records, moyennes, progression — puis on branche le tout avec TanStack Query (oui, lui aussi sert en local).

⏱️ TL;DR — Des agrégats SQL classiques (MAX, AVG, GROUP BY) font tout le travail : record par thème, taux de réussite, sessions par jour. Côté React, on réutilise TanStack Query comme couche d’accès (queryKey ['stats', …], invalidation après chaque partie) — un seul modèle mental pour les données distantes ET locales. L’écran résultat sait dire « 🏆 Nouveau record ! ».

🎯 Objectifs

• Écrire les requêtes d’agrégats de Drill (records, stats par thème, progression).
• Utiliser Query pour les données SQLite (et invalider après écriture).
• Détecter le nouveau record au bon moment (avant d’écrire la session !).
• Brancher l’Historique définitif : liste + en-tête de stats.

Les requêtes de Drill

// lib/db/stats.ts
import type { SQLiteDatabase } from 'expo-sqlite'
 
export type ThemeStats = {
  theme: string
  sessionCount: number
  bestScore: number
  avgScore: number
  accuracy: number          // 0-1, toutes sessions confondues
  lastPlayedAt: number | null
}
 
export function getThemeStats(db: SQLiteDatabase, theme: string) {
  return db.getFirstAsync<ThemeStats>(
    `SELECT theme,
            COUNT(*)                              AS sessionCount,
            MAX(score)                            AS bestScore,
            ROUND(AVG(score))                     AS avgScore,
            CAST(SUM(correct_count) AS REAL) / NULLIF(SUM(total), 0) AS accuracy,
            MAX(played_at)                        AS lastPlayedAt
     FROM sessions WHERE theme = ? GROUP BY theme`, theme
  )
}
 
export function getGlobalStats(db: SQLiteDatabase) {
  return db.getAllAsync<ThemeStats>(
    `SELECT theme, COUNT(*) AS sessionCount, MAX(score) AS bestScore,
            ROUND(AVG(score)) AS avgScore,
            CAST(SUM(correct_count) AS REAL) / NULLIF(SUM(total), 0) AS accuracy,
            MAX(played_at) AS lastPlayedAt
     FROM sessions GROUP BY theme ORDER BY lastPlayedAt DESC`
  )
}
 
// Les questions les plus ratées — le radar de révision (et la graine de Drill v2)
export function getWorstQuestions(db: SQLiteDatabase, limit = 10) {
  return db.getAllAsync<{ questionId: string; attempts: number; failRate: number }>(
    `SELECT question_id                       AS questionId,
            COUNT(*)                          AS attempts,
            1.0 - AVG(correct)                AS failRate
     FROM answers
     GROUP BY question_id
     HAVING attempts >= 3                     -- pas de verdict sur 1 essai
     ORDER BY failRate DESC, attempts DESC
     LIMIT ?`, limit
  )
}

Trois détails de SQL qui distinguent le code sérieux :

• NULLIF(SUM(total), 0) : pas de division par zéro (table vide).
• CAST(… AS REAL) : sans lui, la division INTEGER/INTEGER tronque à 0.
• HAVING attempts >= 3 : un agrégat ne devient une information qu’avec assez de données.
• Et AVG(correct) sur du 0/1 = le taux de réussite direct — l’astuce booléens-entiers.

TanStack Query pour le local — un seul modèle mental

Surprise de design : Query n’est pas réservé au réseau. queryFn est juste une fonction async — SQLite en est une. Bénéfices : cache entre écrans, états gérés, et surtout l’invalidation :

// hooks/stats.ts
import { useQuery, useQueryClient, useMutation } from '@tanstack/react-query'
import { useSQLiteContext } from 'expo-sqlite'
import { getGlobalStats, getThemeStats } from '../lib/db/stats'
import { insertSession } from '../lib/db/sessions'
 
export function useGlobalStats() {
  const db = useSQLiteContext()
  return useQuery({
    queryKey: ['stats', 'global'],
    queryFn: () => getGlobalStats(db),
    staleTime: Infinity,          // ← n'expire JAMAIS seul : seule une partie le change
  })
}
 
export function useSaveSession() {
  const db = useSQLiteContext()
  const qc = useQueryClient()
  return useMutation({
    mutationFn: ({ summary, answers }: { summary: SessionInput; answers: AnswerInput[] }) =>
      insertSession(db, summary, answers),
    onSuccess: () => {
      qc.invalidateQueries({ queryKey: ['stats'] })      // TOUTES les stats se rafraîchissent
      qc.invalidateQueries({ queryKey: ['history'] })
    },
  })
}

staleTime: Infinity + invalidation explicite : les stats locales ne changent QUE quand une partie s’enregistre — le cache est donc éternellement frais entre-temps, et l’invalidation après mutation rafraîchit tous les écrans abonnés d’un coup. (C’est LE pattern mutation/invalidation de Query, appliqué à une base locale.)

Le nouveau record — l’ordre des opérations

L’écran résultat veut afficher « 🏆 Nouveau record ! ». Subtilité : il faut comparer AVANT d’insérer (sinon la session courante est son propre record) :

// Dans le flux de fin de partie (écran résultat, au montage)
const previousBest = await getThemeStats(db, theme).then(s => s?.bestScore ?? 0)
const isNewRecord = summary.score > previousBest
await saveSession.mutateAsync({ summary, answers })
// → afficher isNewRecord (et le confetti attendra la Partie 10)

Séquence : lire l’ancien record → comparer → écrire. Trois lignes, mais l’inverser est LE bug du domaine (tout le monde le fait une fois).

L’Historique définitif assemble tout : ListHeaderComponent = les cartes de stats globales (useGlobalStats), la liste = useQuery(['history'], () => listSessions(db)), le useFocusEffect du chapitre 6.1 devient inutile — l’invalidation a remplacé le refetch au focus : plus précis, plus économe.

⚠️ Piège — Résistez à « je calcule les stats en JS depuis listSessions() ». Ça marche à 50 sessions… et l’écran ramera à 2 000 (tout charger pour tout réduire). SQLite est un moteur d’agrégation : GROUP BY sur 100 000 lignes indexées est instantané. La règle : la réduction se fait dans la base, le JS reçoit des résultats, pas des données brutes.

💡 Pour un dev Next.js — Query pour SQLite peut surprendre — mais reconnaissez le pattern : c’est exactement invalidation-après-mutation que vous pratiquez avec les server actions + revalidate. Un seul modèle mental (queryKey/invalidate) pour GitHub ET la base locale, c’est moins de concepts que « un système de cache réseau + un state manager local ».

✏️ Exercices

1. Écrivez getDailyActivity(db, days) : sessions par jour sur les N derniers jours (pour la future grille d’activité façon GitHub). Astuce : strftime sur played_at/1000.

export function getDailyActivity(db: SQLiteDatabase, days = 30) {
  return db.getAllAsync<{ day: string; count: number }>(
    `SELECT strftime('%Y-%m-%d', played_at / 1000, 'unixepoch', 'localtime') AS day,
            COUNT(*) AS count
     FROM sessions
     WHERE played_at >= ?
     GROUP BY day ORDER BY day`,
    Date.now() - days * 86_400_000
  )
}

2. L’écran détail d’un thème (ch. 5.3) doit afficher son record réel et le taux de réussite. Branchez useThemeStats(slug) — gérez le cas « jamais joué ».

export function useThemeStats(theme: string) {
  const db = useSQLiteContext()
  return useQuery({
    queryKey: ['stats', 'theme', theme],
    queryFn: () => getThemeStats(db, theme),
    staleTime: Infinity,
  })
}

3. Un utilisateur joue en pleine nuit : sa session de 00h30 compte pour quel jour dans getDailyActivity, et pourquoi est-ce le bon comportement ? Quel bug le paramètre ‘localtime’ évite-t-il ?

🧠 Quiz

1. Pourquoi CAST(… AS REAL) dans le calcul d’accuracy ?

2. Pourquoi staleTime: Infinity sur les stats locales ?

3. Quel est l’ordre correct pour détecter un nouveau record ?

4. Où se calculent les agrégats, et pourquoi pas en JS ?

5. Que remplace l’invalidation par rapport au chapitre 6.1 ?

👉 Chapitre suivant : 8.4 — Drizzle ORM — le confort typé, et quand il vaut son coût.