SELECT DISTINCT e.user_id
FROM emails e
WHERE EXISTS (
  SELECT 1
  FROM texts t1
  WHERE t1.email_id = e.email_id
    AND t1.signup_action = 'Confirmed'
    AND DATE(t1.action_date) = DATE(e.signup_date + INTERVAL '1 day')  -- подтвердил на 2-й день
)
AND NOT EXISTS (
  SELECT 1
  FROM texts t0
  WHERE t0.email_id = e.email_id
    AND t0.signup_action = 'Confirmed'
    AND DATE(t0.action_date) = DATE(e.signup_date)                      -- не подтвердил в день регистрации
);

SELECT e.user_id
FROM emails e
JOIN texts  t ON t.email_id = e.email_id
GROUP BY e.user_id, e.signup_date
HAVING COUNT(*) FILTER (
  WHERE t.signup_action = 'Confirmed' AND DATE(t.action_date) = DATE(e.signup_date)
) = 0
AND COUNT(*) FILTER (
  WHERE t.signup_action = 'Confirmed' AND DATE(t.action_date) = DATE(e.signup_date + INTERVAL '1 day')
) >= 1;

WITH numbered AS (
    SELECT 
        id,
        email,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
)
SELECT id, email
FROM numbered
WHERE rn = 1;

SELECT 
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) AS running_total
FROM orders;

SELECT DISTINCT ON (customer_id) 
    customer_id,
    order_date,
    amount
FROM orders
ORDER BY customer_id, order_date DESC;

SELECT DISTINCT ON (customer_id) 
       customer_id, order_id, created_at
FROM orders
ORDER BY customer_id, created_at DESC;

SELECT category_id, product_id, price
FROM products
QUALIFY ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) <= 2;