SELECT *
FROM events
WHERE data->'user'->>'email' = 'user@example.com';

CREATE INDEX idx_events_user_email
ON events ((data->'user'->>'email'));

SELECT *
FROM events
WHERE data->'user'->>'email' = 'user@example.com';

CREATE INDEX idx_events_data_gin
ON events USING GIN (data jsonb_path_ops);

SELECT *
FROM events
WHERE data @> '{"user": {"email": "user@example.com"}}';

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;