orders (
  order_id INT,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL
)

WITH monthly_orders AS (
  SELECT
    customer_id,
    TO_CHAR(order_date, 'YYYY-MM') AS order_month,
    DATE_TRUNC('month', order_date) AS month_start,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sum
  FROM orders
  GROUP BY customer_id, TO_CHAR(order_date, 'YYYY-MM'), DATE_TRUNC('month', order_date)
),
numbered_months AS (
  SELECT
    customer_id,
    order_month,
    month_start,
    order_count,
    total_sum,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY month_start) AS rn
  FROM monthly_orders
),
three_month_windows AS (
  SELECT
    m1.customer_id,
    m1.order_month AS month1,
    m2.order_month AS month2,
    m3.order_month AS month3,
    (m1.total_sum + m2.total_sum + m3.total_sum) AS total_window_sum,
    (m1.order_count + m2.order_count + m3.order_count) AS total_orders
  FROM numbered_months m1
  JOIN numbered_months m2 ON m1.customer_id = m2.customer_id AND m2.rn = m1.rn + 1
  JOIN numbered_months m3 ON m1.customer_id = m3.customer_id AND m3.rn = m1.rn + 2
  WHERE
    m3.month_start - m1.month_start <= INTERVAL '62 days'  -- ~2 месяца
)
SELECT
  customer_id,
  month1,
  month2,
  month3,
  total_orders,
  total_window_sum
FROM three_month_windows
WHERE total_window_sum > 10000;

SELECT category, COUNT(*) AS freq
FROM products
GROUP BY category
ORDER BY freq DESC;

SELECT user_id, COUNT(*) AS actions
FROM logs
GROUP BY user_id
ORDER BY actions DESC
LIMIT 10;

git clone https://github.com/microsoft/documentdb.git

docker build . -f .devcontainer/Dockerfile -t documentdb  
docker run -v $(pwd):/home/documentdb/code -it documentdb /bin/bash
cd code && make && sudo make install
./scripts/start_oss_server.sh -t documentdb

psql -p 9712 -h localhost -d postgres

WITH login_ranked AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM logins
),
grouped AS (
    SELECT
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL rn DAY) AS grp
    FROM login_ranked
),
group_lengths AS (
    SELECT
        user_id,
        grp,
        COUNT(*) AS streak_length
    FROM grouped
    GROUP BY user_id, grp
)
SELECT
    user_id,
    MAX(streak_length) AS max_consecutive_days
FROM group_lengths
GROUP BY user_id
ORDER BY max_consecutive_days DESC;