SQL и Анализ данных

🧠 Хитрая SQL-задача на собеседование: "Пропущенный день"

Представь таблицу заказов:

orders (
  id INT,
  user_id INT,
  order_date DATE
)

🎯 Задача: Найти пользователей, у которых были два заказа, сделанных с пропуском ровно одного дня
(например: 1 мая и 3 мая), но без других заказов между ними.

🛑 Не учитываются пары, где между ними был хоть один заказ.

---

💡 Решение с LEAD() + NOT EXISTS:


WITH ranked_orders AS (
  SELECT
    user_id,
    order_date,
    LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS next_order
  FROM orders
),
valid_pairs AS (
  SELECT
    user_id
  FROM ranked_orders ro
  WHERE DATE_DIFF(ro.next_order, ro.order_date, DAY) = 2
    AND NOT EXISTS (
      SELECT 1 FROM orders o
      WHERE o.user_id = ro.user_id
        AND o.order_date > ro.order_date
        AND o.order_date < ro.next_order
    )
)
SELECT DISTINCT user_id FROM valid_pairs;

🔍 Подвох: многие находят заказы “через день”, но забывают проверить, не было ли чего-то между ними.
Такие задачки отлично проверяют знание оконных функций и аналитического мышления.

❤11👍5🔥2

3.25K views16:20

SQL и Анализ данных

🧠 Продвинутая SQL-задача: "Скользящий максимум с условиями"

Допустим, у вас есть таблица transactions:


CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    user_id INT,
    amount NUMERIC,
    status TEXT,
    created_at TIMESTAMP
);

🎯 Задача:
Для каждой транзакции нужно найти максимальный amount:
- За последние 7 дней, включая текущую
- Только по этому же user_id
- Только по статусу 'confirmed'

📥 Ожидаемый вывод:

| id | user_id | amount | created_at         | max_amount_last_7d |
|----|---------|--------|--------------------|---------------------|
| 1  | 101     | 100    | 2024-01-01 10:00   | 100                 |
| 2  | 101     | 150    | 2024-01-04 12:00   | 150                 |
| 3  | 101     | 120    | 2024-01-07 09:00   | 150                 |
| 4  | 101     | 90     | 2024-01-15 14:00   | 120                 |

🧩 Решение:


SELECT
    t1.id,
    t1.user_id,
    t1.amount,
    t1.created_at,
    (
        SELECT MAX(t2.amount)
        FROM transactions t2
        WHERE t2.user_id = t1.user_id
          AND t2.status = 'confirmed'
          AND t2.created_at BETWEEN t1.created_at - INTERVAL '7 days' AND t1.created_at
    ) AS max_amount_last_7d
FROM transactions t1;

⚠️ Подводные камни:
- Без индексов по user_id, status, created_at запрос будет тормозить
- Используется подзапрос, а не window function, потому что окно — динамическое
- В PostgreSQL стоит учитывать точные границы BETWEEN при сравнении времени

🚀 Навыки, которые проверяются:
- Оптимизация временных окон
- Группировка и фильтрация с условиями
- Агрегации по диапазонам

💡 Усложнение:
Хочешь ещё интереснее? Добавь группировку по разным status, или перепиши на JOIN с LATERAL.

🔥9❤6🥰2😱1

3.42K views09:05

SQL и Анализ данных

🖥

WrenAI — это open-source AI-ассистент для анализа данных и генерации SQL-запросов (Text-to-SQL)!

🌟 Он позволяет пользователям взаимодействовать с данными с помощью естественного языка и автоматически формировать SQL-запросы, строить диаграммы, отчеты и таблицы.

🔐 Лицензия: AGPL-3.0

🖥

Github

Please open Telegram to view this post