Réponses conservées
Rapports sous surveillance
Injection du formulaire, création d'un schéma et d'une table miroir, filtrage par cible puis comptage des enquêteurs associés.
Le projet en quelques mots
DataClash prend la forme d'une enquête dans la ville fictive de Mementos. Après avoir rejoint la faction gouvernementale NERDS, chaque étape fournit un nouveau jeu de données et une série de questions à résoudre en SQL.
Le rendu conservé commence par l'extraction de rapports depuis une application vulnérable, puis leur copie dans une base locale. Il poursuit avec le filtrage de positions, le calcul de distances et l'étude de déplacements successifs.
Une troisième étape aborde l'analyse d'archives et d'incohérences temporelles, mais plusieurs réponses de cette partie sont incomplètes. Le sujet d'optimisation est présent dans l'archive, sans les requêtes de partitionnement et d'indexation attendues.
Progression de l'enquête
Les exercices s'enchaînent autour de jeux de données différents. Ce schéma distingue les requêtes réellement conservées des objectifs restés sans livrable.
Réponses conservées
Parcours géographique
Sélection des positions dans un rayon donné, calcul des distances
et rapprochement des points consécutifs avec LEAD.
Partiel
Archives de l'affaire Napoleon
Des requêtes traitent les anomalies et la chronologie. Le croisement des personnes et la vue de synthèse restent des réponses factices.
Non conservé
Optimisation
Le sujet demande des partitions et neuf index PostgreSQL, mais aucun fichier de réponse à cette étape n'est présent.
Ce qui est présent
Schéma et table miroir
Le SQL crée le schéma investigation, un type énuméré pour
le niveau de danger et une table destinée aux rapports récupérés.
Filtrer et compter
Les rapports sont filtrés sur l'identifiant recherché, puis regroupés
par enquêteur avec GROUP BY et triés par nombre de rapports.
Distance entre coordonnées
Les distances sont calculées directement avec acos,
cos, sin et radians, sans PostGIS.
Points consécutifs
LEAD et LAG rapprochent chaque ligne de la suivante
ou de la précédente pour mesurer une durée et repérer des incohérences.
Classification d'anomalies
Une expression CASE classe notamment les caractères invalides,
dates manquantes, lieux absents, valeurs improbables et doublons.
Requêtes composées
Plusieurs traitements sont décomposés avec des expressions de table communes afin de filtrer, calculer puis présenter les résultats.
État du rendu conservé
Étapes 1 et 2
Cinq fichiers SQL et la charge d'injection couvrent la table miroir, le filtrage, l'agrégation et l'analyse des positions.
Étape 3
Cinq fichiers existent. L'analyse d'anomalies et la chronologie contiennent une logique réelle ; les connexions sont codées en dur et la vue de synthèse ne calcule pas les résultats demandés.
Étape 4
Seul le sujet est archivé. Aucun script de partitionnement, d'indexation ou d'analyse finale n'accompagne le rendu.
Relier deux positions successives
Cet extrait abrégé du rendu utilise une fonction fenêtre pour récupérer la prochaine position et calculer le temps écoulé depuis le point courant.
SELECT
latitude AS start_lat,
longitude AS start_lon,
LEAD(latitude, 1) OVER (ORDER BY timestamp) AS end_lat,
LEAD(longitude, 1) OVER (ORDER BY timestamp) AS end_lon,
EXTRACT(EPOCH FROM
LEAD(timestamp, 1) OVER (ORDER BY timestamp) - timestamp
) AS duration,
timestamp
FROM filtered_points;Synthèse du projet
Travail réalisé
Le rendu rassemble une série de requêtes PostgreSQL pour reconstruire et interroger des données de rapports, puis analyser des coordonnées et des événements successifs. Les deux premières étapes constituent la partie la plus aboutie de l'archive conservée.
Apports techniques
Le projet fait pratiquer la création de schémas et de types, les agrégations, les CTE, les fonctions fenêtre et la traduction de règles métier en expressions SQL conditionnelles.