Requêtes classiques d'entretien

SELECT DISTINCT salaire
FROM   employes
ORDER  BY salaire DESC
OFFSET 1 LIMIT 1;

-- Variante robuste aux ex æquo avec une fonction de fenêtre
SELECT salaire FROM (
    SELECT salaire, dense_rank() OVER (ORDER BY salaire DESC) AS r FROM employes
) t WHERE r = 2 LIMIT 1;

DELETE FROM utilisateurs u
USING (
    SELECT id, row_number() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM   utilisateurs
) d
WHERE u.id = d.id AND d.rn > 1;          -- garde la plus ancienne ligne de chaque email

SELECT jour, ca,
       sum(ca) OVER (ORDER BY jour)                        AS cumul,
       round(100.0 * ca / sum(ca) OVER (), 1)              AS part_pct
FROM   ventes_par_jour;

SELECT utilisateur_id, min(jour) AS debut, max(jour) AS fin, count(*) AS nb_jours
FROM (
    SELECT utilisateur_id, jour,
           jour - (row_number() OVER (PARTITION BY utilisateur_id ORDER BY jour))::int AS groupe
    FROM   (SELECT DISTINCT utilisateur_id, jour FROM connexions) c
) t
GROUP  BY utilisateur_id, groupe
ORDER  BY utilisateur_id, debut;

SELECT c.id, c.nom
FROM   clients c
WHERE  NOT EXISTS (
    SELECT 1 FROM commandes o
    WHERE  o.client_id = c.id AND o.cree_le >= now() - interval '90 days'
);

SELECT client_id,
       count(*) FILTER (WHERE statut = 'payee')   AS payees,
       count(*) FILTER (WHERE statut = 'annulee') AS annulees
FROM   commandes
GROUP  BY client_id;