Résoudre l'interview SQL de Facebook - Search Ranking

Hello les data enthousiastes! 

Je suis de retour avec une nouvelle résolution détaillée d’une question SQL posée par Facebook. Je dois avouer qu’il m’a fallu plusieurs sessions pour venir à bout de cette exercice (noté HARD sur le site Interview Query).

Au final, quelques astuces SQL et une bonne compréhension de l’énoncé m’ont permis de venir à bout de la question. Regardons cela ensemble!

Notre question aujourd'hui est la suivante : Calculer le pourcentage de requête ayant les ratings les plus pertinents. Je vais construire un script SQL qui s'exécutera directement sur la base de données pour obtenir le résultat attendu.

Une présentation d'Interview Query pour SQL

Depuis quelques années, j'utilise Interview Query (ce post n'est pas sponsorisé). C'est une plateforme d'apprentissage pour préparer les interviews techniques. D'Amazon à Google, ils disposent d'une vaste base de données de questions et d'exercices disponibles. Cette question vient de leur plateforme et ils fournissent également les données à utiliser, ce qui est très pratique. Vous pouvez exécuter directement les requêtes et voir le résultat final.

Je voulais simplement partager avec vous cette plateforme car c'est un super outil pour les ingénieurs que nous sommes. Maintenant, revenons à notre question principale.

Commençons vraiment, cette fois !

Etape 1 : Les requêtes aux meilleures ratings

J'ai commencé par filtrer les lignes pour obtenir le périmètre demandé. Pour cela, j’ai récupéré les query dont tout les ratings sont inférieurs à 3. Une fois cette étape traitée, nous devons déterminer le pourcentage que représente ce périmètre par rapport à l'intégralité des query.

with queries_above_three as (
    SELECT query
    FROM search_results
    WHERE rating >= 3 
    GROUP BY query
)
    SELECT 'total' as ratio, count(DISTINCT query) as tt_threshold
    FROM search_results
    WHERE rating < 3 and query not in (SELECT * FROM queries_above_three)

Etape 2: Calculer le pourcentage de meilleur recherche

Dans cet exercice, il convient de calculer le rapport du nombre de query strictement inférieur à 3 (notre numérateur final) sur le total de requêtes de la table (notre dénominateur final). Nous avons désormais la première partie de la réponse, dans les sous-requêtes, il est important de formatter correctement les valeurs avant l’étape finale :

  SELECT cast(tt_threshold as numeric), cast(tt_line as numeric)
  FROM queries_below_three as qbt
  INNER JOIN (
    SELECT 'total' as ratio, COUNT(DISTINCT QUERY) as tt_line
    FROM search_results
  ) sub_tb ON qbt.ratio = sub_tb.ratio

Etape 3 : Une jointure et une division

Il ne reste plus qu'à calculer notre division avec les éléments précédents et à imbriquer l'ensemble des requêtes dans un clause WITH pour une meilleure visibilité et maintenance. Pour amélioration, on peut trouver des noms de tables plus parlants que tb par exemple.

with queries_above_three as (
    SELECT query
    FROM search_results
    WHERE rating >= 3 
  GROUP BY query
), queries_below_three as (
    SELECT 'total' as ratio, count(DISTINCT query) as tt_threshold
    FROM search_results
    WHERE rating < 3 and query not in (SELECT * FROM queries_above_three)	
)
SELECT round(cast(tt_threshold / tt_line as numeric),2) as percentage_less_than_3
FROM (
  SELECT cast(tt_threshold as numeric), cast(tt_line as numeric)
  FROM queries_below_three as qbt
  INNER JOIN (
    SELECT 'total' as ratio, COUNT(DISTINCT QUERY) as tt_line
    FROM search_results
  ) sub_tb ON qbt.ratio = sub_tb.ratio
) sub_tb2;

Il m'a fallut plusieurs essais pour parvenir à résoudre cet exercice. Je vous conseil de tenter plusieurs approches sans jamais baisser les bras!

Ressources :

• code accessible sur mon Github

• exercice dans Interview Query

Retrouvons-nous au prochain exercice !