Résoudre l'interview SQL d'Amazon - Customer Orders

Régulièrement entretenir ses compétences techniques est nécessaire en informatique. Pour bien commencer sa carrière en data, SQL est le point de départ. Aujourd'hui, je vais résoudre un exercice SQL issu d'une interview technique d'Amazon.

En tant qu'ingénieur de données, le SQL mon langage de prédilection. Je l'utilise tous les jours pour de nombreuses tâches. Comme pour tout langage, la pratique est importante pour maintenir son niveau.

Notre question aujourd'hui est la suivante : Identifier les clients qui ont effectué plus de trois transactions ce 2019 à 2020. La base de données ressemble à ce qui suit :

Le résultat attendu est une colonne contenant le nom des clients correspondant.

Je vais construire un script SQL qui s'exécutera directement sur la base de données pour obtenir le résultat attendu.

Commençons à coder !

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 : Gardons les transactions de 2019 à 2020

Le volume de données est une valeur pertinente à savoir. Mais pourquoi ? Parce que j'ai besoin d'avoir une connaissance globale de la taille de la base de données. Je ne pense pas immédiatement à l'optimisation, mais ces chiffres me donneront un ordre d'idée pour la réponse. Si le volume est incohérent avec cela, alors je sais que j'ai dû faire une erreur quelque part. Pour connaître le nombre de transactions par année, utilisez la requête suivante:

SELECT YEAR(created_at) as year, count(*) as nb_transactions
FROM transactions
WHERE YEAR(created_at) IN (2019,2020)
GROUP BY YEAR(created_at)
ORDER BY YEAR(created_at) ASC;

Etape 2: Focus sur 2019 et récupération des clients

Observons combien de transactions nos clients ont effectuées en 2019. Pour ce faire, ajoutons le nom du client (car c'est mieux qu'un identifiant) et comptons combien de transactions nous trouvons.

SELECT name, count(*) as nb_transactions
FROM transactions as t
INNER JOIN users as u ON t.user_id = u.id
WHERE YEAR(created_at) = 2019
GROUP BY name
ORDER BY count(*) ASC;

Concentrons-nous maintenant sur la deuxième partie de la question. La logique est la même pour chaque année, vous pouvez dupliquer la requête de 2019 et en changer l'année.

-- Situation in 2019
SELECT name 
FROM transactions as t
INNER JOIN users as u ON t.user_id = u.id
WHERE YEAR(created_at) = 2019
GROUP BY name, YEAR(created_at)
HAVING COUNT(*) > 3 
ORDER BY name ASC;

-- Situation in 2020
SELECT name 
FROM transactions as t
INNER JOIN users as u ON t.user_id = u.id
WHERE YEAR(created_at) = 2020
GROUP BY name, YEAR(created_at)
HAVING COUNT(*) > 3 
ORDER BY name ASC;

Chaque requête me donne les clients qui ont effectué plus de trois transactions. Ci-dessous, les résultats basés sur mon échantillon de données (je les ai exécutées directement sur une base de données fictive, mais si vous utilisez Interview Query, le résultat sera différent).

Pour 2019, nous avons le résultat suivant :

• Cammy Whitelaw

• Darby Rayman

• Erminia Cowterd

• Everlyn Kembley

• Jewell Karim

• Ronnie Simper

Pour 2020, nous avons le résultat suivant :

• Darby Rayman

• Erminia Cowterd

• Huberto Bradnum

Maintenant, la dernière étape consiste à sélectionner uniquement les clients qui apparaissent dans les deux résultats. Donc, selon mon exemple, la réponse finale est :

• Darby Rayman

• Erminia Cowterd

Etape 3: Identifier les clients répondant à la question

Nous avons plusieurs options pour fournir le résultat. Je suppose que la question est ponctuelle et de ce fait, je construis une requête pour y répondre. Auquel cas, j'aurais créé une vue.

-- List of customers with more than 3 transactions in both 2019 and 2020
SELECT cust_2019.name as name
FROM (
  -- Situation in 2019
  SELECT name --, count(*) as nb_transactions
  FROM transactions as t
  INNER JOIN users as u ON t.user_id = u.id
  WHERE YEAR(created_at) = 2019
  GROUP BY name, YEAR(created_at)
  HAVING COUNT(*) > 3
  -- ORDER BY name ASC;
) as cust_2019
INNER JOIN (
  -- Situation in 2020
  SELECT name --, count(*) as nb_transactions
  FROM transactions as t
  INNER JOIN users as u ON t.user_id = u.id
  WHERE YEAR(created_at) = 2020
  GROUP BY name, YEAR(created_at)
  HAVING COUNT(*) > 3 
  -- ORDER BY name ASC;
) AS cust_2020 
ON cust_2019.name = cust_2020.name;

Dernière étape: Révision et Optimisation

Parfait ! Nous avons répondu à la question mais regardons à nouveau notre requête. Peut-être que faire le JOIN si tôt n'est pas pertinent. En suivant la même logique, nous pouvons travailler avec la table des transactions et manipuler l'ID utilisateur au lieu du nom. Donc, une alternative possible est la suivante :

SELECT u.name as customer_name
FROM (
  -- Situation in 2019
  SELECT user_id 
  FROM transactions 
  WHERE YEAR(created_at) = 2019
  GROUP BY user_id, YEAR(created_at)
  HAVING COUNT(*) > 3
) as cust_2019

INNER JOIN (
  -- Situation in 2020
  SELECT user_id
  FROM transactions 
  WHERE YEAR(created_at) = 2020
  GROUP BY user_id, YEAR(created_at)
  HAVING COUNT(*) > 3 
) AS cust_2020 ON cust_2019.user_id = cust_2020.user_id

INNER JOIN users as u ON cust_2019.user_id = u.id ;

Le résultat final est la liste des clients ci-dessous. Félicitations, vous avez terminé cet exercice proposé par Amazon !

CUSTOMER

Darby Rayman

Erminia Cowterd

Retrouvons-nous au prochain exercice !

source: direct link