-- Найдём email-адреса, у которых более одного уникального user_id
SELECT email
FROM users
GROUP BY email
HAVING COUNT(DISTINCT user_id) > 1;

-- Если нужно вытащить сами строки — можно использовать подзапрос
SELECT *
FROM users
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(DISTINCT user_id) > 1
);

SELECT customer_id, MAX(total)
FROM orders
GROUP BY customer_id;

SELECT *
FROM orders
GROUP BY customer_id
HAVING total = MAX(total);

SELECT o.*
FROM orders o
JOIN (
SELECT customer_id, MAX(total) AS max_total
FROM orders
GROUP BY customer_id
) t ON o.customer_id = t.customer_id AND o.total = t.max_total;

-- Демоданные
WITH items AS (
  SELECT 101 item_id, 50 init_qty FROM dual UNION ALL
  SELECT 102,  5 FROM dual
),
movements_raw AS (
  SELECT 101 item_id, DATE'2025-08-01' ts,  30 qty FROM dual UNION ALL -- приход
  SELECT 101, DATE'2025-08-02',            -20     FROM dual UNION ALL -- расход
  SELECT 101, DATE'2025-08-04',            -40     FROM dual UNION ALL -- перерасход → stockout
  SELECT 101, DATE'2025-08-06',             60     FROM dual UNION ALL -- пополнение
  SELECT 102, DATE'2025-08-01',            - 2     FROM dual UNION ALL
  SELECT 102, DATE'2025-08-03',            - 3     FROM dual UNION ALL
  SELECT 102, DATE'2025-08-05',              5     FROM dual
),
-- 1) Свернём движения по дням (может быть несколько событий в день)
movements AS (
  SELECT item_id, ts, SUM(qty) qty_per_day
  FROM movements_raw
  GROUP BY item_id, ts
),

-- 1) Календарь на ежедневной сетке для каждого товара
date_bounds AS (
  SELECT item_id,
         LEAST(MIN(ts), TRUNC(SYSDATE)) AS dmin,
         GREATEST(MAX(ts), TRUNC(SYSDATE)) AS dmax
  FROM movements
  GROUP BY item_id
),
calendar AS (
  SELECT b.item_id, (b.dmin + LEVEL - 1) d
  FROM date_bounds b
  CONNECT BY LEVEL <= b.dmax - b.dmin + 1
  AND PRIOR item_id = item_id
  AND PRIOR SYS_GUID() IS NOT NULL
),

-- 2) Посчитаем ежедневный остаток MODEL-ом
onhand AS (
  SELECT item_id, d, onhand, qty_per_day
  FROM (
    SELECT c.item_id,
           c.d,
           NVL(m.qty_per_day, 0) qty_per_day,
           i.init_qty
    FROM calendar c
    JOIN items i USING(item_id)
    LEFT JOIN movements m ON m.item_id = c.item_id AND m.ts = c.d
  )
  MODEL
    PARTITION BY (item_id)
    DIMENSION BY (d)
    MEASURES (init_qty, qty_per_day, CAST(NULL AS NUMBER) AS onhand)
    RULES SEQUENTIAL ORDER (
      onhand[MIN(d)] = init_qty[MIN(d)] + qty_per_day[MIN(d)],
      onhand[FOR d FROM MIN(d)+1 TO MAX(d)] =
        onhand[CV(d)-1] + qty_per_day[CV(d)]
    )
),

-- 3) Найдём окна нулевого остатка и их длины
stockout_runs AS (
  SELECT *
  FROM onhand
  MATCH_RECOGNIZE (
    PARTITION BY item_id
    ORDER BY d
    MEASURES
      FIRST(d) AS start_d,
      LAST(d)  AS end_d,
      COUNT(*) AS days_zero
    ONE ROW PER MATCH
    PATTERN (z+)
    DEFINE
      z AS onhand = 0
  )
),

-- 3) Для самого длинного окна найдём дату первого пополнения после него
longest_zero AS (
  SELECT s.item_id, s.start_d, s.end_d, s.days_zero,
         LEAD(s.end_d) OVER (PARTITION BY s.item_id ORDER BY s.days_zero, s.end_d) dummy
  FROM (
    SELECT s.*, ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY days_zero DESC, end_d) rn
    FROM stock