-- Дни без заказов за последние 30 дней
WITH calendar AS (
  SELECT generate_series(
    current_date - interval '30 days',
    current_date,
    interval '1 day'
  )::date AS day
),
orders_per_day AS (
  SELECT
    order_ts::date AS day,
    COUNT(*)       AS orders_count
  FROM sales
  WHERE order_ts >= current_date - interval '30 days'
  GROUP BY order_ts::date
)
SELECT
  c.day,
  COALESCE(o.orders_count, 0) AS orders_count
FROM calendar c
LEFT JOIN orders_per_day o USING(day)
WHERE o.orders_count IS NULL
ORDER BY c.day;

select symbol,
       max(price) keep (dense_rank last order by ts) as last_price,
       max(ts)    as last_ts
from trades
group by symbol;

select trunc(ts) as trade_day,
       symbol,
       min(price) keep (dense_rank first order by ts) as first_price
from trades
group by trunc(ts), symbol;

pip install sqlalchemy pandas
from typing import Iterable, Sequence, Dict, Any
import pandas as pd
from sqlalchemy import create_engine, text, bindparam, tuple_
from sqlalchemy.engine import Engine, Result

# Пример: engine для Postgres/Oracle/MySQL и т.д.
# engine = create_engine("postgresql+psycopg://user:pass@host/db")
# engine = create_engine("oracle+oracledb://user:pass@tnsname")

def stream_query_in(engine: Engine, table: str, id_list: Sequence[int], chunk_rows: int = 50_000) -> Iterable[pd.DataFrame]:
    """
    Потоково выбирает строки по большому списку ID:
    - безопасно подставляет массив в IN (:ids) через expanding=True
    - возвращает пачки DataFrame фиксированного размера (chunk_rows)
    """
    # 1) Готовим SQL с расширяемым параметром
    stm = text(f"SELECT * FROM {table} WHERE id IN :ids").bindparams(
        bindparam("ids", expanding=True)
    )
    with engine.connect() as conn:
        # 2) Выполняем запрос одним списком (драйвер сам развернёт IN)
        result: Result = conn.execution_options(stream_results=True).execute(stm, {"ids": list(id_list)})

        # 3) Потоково собираем фреймы по chunk_rows строк
        batch = []
        for row in result:
            batch.append(dict(row._mapping))
            if len(batch) >= chunk_rows:
                yield pd.DataFrame.from_records(batch)
                batch.clear()
        if batch:
            yield pd.DataFrame.from_records(batch)

def fetch_df_in(engine: Engine, table: str, id_list: Sequence[int]) -> pd.DataFrame:
    """Если нужен единый DataFrame — аккуратно склеиваем поток."""
    frames = list(stream_query_in(engine, table, id_list))
    return pd.concat(frames, ignore_index=True) if frames else pd.DataFrame()

# Пример использования:
# big_ids = range(1, 2_000_000)
# for df_chunk in stream_query_in(engine, "orders", big_ids, chunk_rows=100_000):
#     process(df_chunk)  # обрабатываем пачками без OOM

# Бонус: многоколонночный IN (tuple IN) без ручной генерации SQL
def stream_query_tuple_in(engine: Engine, table: str, pairs: Sequence[Dict[str, Any]]):
    """
    pairs: [{ "country":"US", "city":"NYC" }, ...]
    SELECT * FROM table WHERE (country, city) IN ((:country_1,:city_1), ...)
    """
    stm = text(f"""
        SELECT * FROM {table}
        WHERE (country, city) IN :keys
    """).bindparams(bindparam("keys", expanding=True))

    # Подготовим данные как кортежи
    key_tuples = [(p["country"], p["city"]) for p in pairs]

    with engine.connect() as conn:
        for row in conn.execution_options(stream_results=True).execute(stm, {"keys": key_tuples}):
            yield dict(row._mapping)

# Пример:
# pairs = [{"country":"US","city":"NYC"}, {"country":"JP","city":"TOKYO"}]
# for rec in stream_query_tuple_in(engine, "warehouses", pairs):
#     ...

SELECT *
FROM (
    SELECT 
        customer_id,
        order_id,
        order_date,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
) t
WHERE rn = 1;

-- PostgreSQL
SELECT 
    DATE_TRUNC('week', order_date) AS week_start,
    COUNT(*) AS orders_count
FROM orders
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY week_start;

-- MySQL
SELECT 
    YEARWEEK(order_date, 1) AS year_week,
    COUNT(*) AS orders_count
FROM orders
GROUP BY YEARWEEK(order_date, 1)
ORDER BY year_week;

SELECT 
    customer_id,
    COUNT(order_id) AS orders_count,
    SUM(amount)     AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000
   AND COUNT(order_id) >= 5;