Как получить вторую загрузку абонента в SQLite?

Условие

В SQLite с поддержкой оконных функций дана таблица loads(id INTEGER PRIMARY KEY, subscriber_id INTEGER NOT NULL, loaded_at TEXT NOT NULL). Даты хранятся в одинаковом ISO-формате и часовом поясе. Найдите вторую с конца загрузку каждого абонента; при равных датах более поздней считается строка с большим id.

Ответ

В SQLite можно пронумеровать загрузки через ROW_NUMBER по subscriber_id в порядке loaded_at DESC, id DESC и выбрать строку с номером 2 во внешнем запросе.

WITH ranked AS (
  SELECT loads.*, ROW_NUMBER() OVER (
    PARTITION BY subscriber_id ORDER BY loaded_at DESC, id DESC
  ) AS rn
  FROM loads
)
SELECT id, subscriber_id, loaded_at FROM ranked WHERE rn = 2;

Абонент с одной загрузкой не попадёт в результат. Вторичный порядок по id устраняет неоднозначность одинаковых дат. Это вторая строка, а не вторая уникальная дата: для другого определения понадобился бы другой контракт ранжирования.

Источник: www.sqlite.org.