Мир аналитика данных: “📊 Задачка с собеседования: Самый дорогой товар в первой покупке Всем…”

📊 Задачка с собеседования: Самый дорогой товар в первой покупке
Всем привет! Сегодня разберём еще одну интересную задачку, которую часто дают на собеседованиях.

🎯 Условие задачи:
Для каждого пользователя нужно найти наименование и цену самого дорогого товара в его первой транзакции.

🔍 Исходные данные:
У нас есть:
Таблица с покупками (df_orders) - кто, когда и что покупал (user_id, transaction_datetime, item_id, order_id)
Табличка товаров (df_items) - информация о товарах и их ценах (item_id, brand, name, price)

💡 Решение с подзапросами:

select user_id,name,price
from
(
select user_id,order_id,transaction_datetime,price,name,item_id,number_bought,
      row_number() OVER (partition by user_id order by price desc) as most_expensive
from(
select user_id,order_id,transaction_datetime, price,name, di.item_id,
       dense_rank() OVER (PARTITION BY user_id order by transaction_datetime ) as number_bought
from df_orders do
left join df_items  di on di.item_id = do.item_id 
) tt
where number_bought=1 
)
where most_expensive=1

🎯 Как это работает?
1️⃣ Внутренний подзапрос:

- DENSE_RANK() нумерует все покупки пользователя по времени.
- Если в одной транзакции куплено несколько товаров, у них будет одинаковый number_bought (например, все =1 для первой покупки).

dense_rank() OVER (PARTITION BY user_id order by transaction_datetime ) as number_bought

2️⃣ Средний подзапрос
- Оставляет только товары из первых покупок (WHERE number_bought = 1).
- ROW_NUMBER() сортирует эти товары по цене (дорогие — выше).

row_number() OVER (partition by user_id order by price desc) as most_expensive

3️⃣Внешний подзапрос выбирает самый дорогой товар из первой покупки (most_expensive=1)

✨ Альтернатива с CTE:

WITH first_purchases AS (
    SELECT
        user_id, price, name,
        DENSE_RANK() OVER (PARTITION BY user_id ORDER BY transaction_datetime) AS number_bought
    FROM df_orders
    LEFT JOIN df_items USING (item_id)
),
most_expensive_items AS (
    SELECT
        user_id, name, price,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY price DESC) AS price_rank
    FROM first_purchases
    WHERE number_bought = 1
)
SELECT user_id, name, price
FROM most_expensive_items
WHERE price_rank = 1

Какой вариант вам ближе — подзапросы или CTE?

P.S. Я сейчас в отпуске 🌴☀️ и принципиально не думаю о SQL. Просто лень не только думать, но и писать. 🤪
(Но код всё равно проверила и протестила)