Как структурировать запрос с промежуточной таблицей для максимальной даты курса

Что проверяют

Умеете ли вы достать последнюю запись в группе и аккуратно присоединить её к основной таблице. Типичная постановка: для каждой валюты взять курс на максимальную дату или для каждой операции найти курс на её дату либо ближайшую предыдущую. Следом спрашивают про дубли дат и поведение запроса на больших объёмах.

Как отвечать

Покажите несколько способов и объясните, когда какой:

• агрегат и join: в CTE SELECT currency, MAX(rate_date) ... GROUP BY currency, затем join обратно по валюте и дате. Просто, но если на одну дату две записи, строки задвоятся;
• оконная функция: ROW_NUMBER() OVER (PARTITION BY currency ORDER BY rate_date DESC) и фильтр rn = 1 снаружи. Дубли не страшны, работает в PostgreSQL, ClickHouse и Spark SQL;
• DISTINCT ON в PostgreSQL: SELECT DISTINCT ON (currency) ... ORDER BY currency, rate_date DESC. Коротко и быстро при индексе по (currency, rate_date);
• курс на дату операции: LEFT JOIN LATERAL с подзапросом WHERE rate_date <= t.op_date ORDER BY rate_date DESC LIMIT 1, в ClickHouse для этого есть ASOF JOIN. Выходные закрываются сами: берётся последний известный курс.

Про промежуточную таблицу: CTE в PostgreSQL с 12 версии встраивается в основной запрос, если не пометить его MATERIALIZED. Временная таблица через CREATE TEMP TABLE оправдана, когда результат нужен нескольким запросам: на неё можно повесить индекс и собрать статистику ANALYZE.

Где спотыкаются

Пишут SELECT currency, rate, MAX(rate_date) ... GROUP BY currency и ждут, что курс возьмётся из той же строки. PostgreSQL такой запрос не выполнит, а там, где он проходит, курс берётся из произвольной строки группы.

Не думают про несколько записей на одну дату и получают лишние строки после join.

Для курса на дату операции делают join по равенству дат и теряют все операции, пришедшиеся на выходные и праздники.