Какой оператор установки для «искал, но не купил»?

SELECT name FROM searchers WHERE product = 'X'
EXCEPT
SELECT name FROM buyers WHERE product = 'X';

-- Scalar subquery (returns one value)
SELECT name, salary,
    (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

-- Correlated subquery (depends on outer query)
SELECT name, salary
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept);

-- Subquery with IN
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 1000);

-- Subquery with EXISTS (often faster than IN for large datasets)
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

WITH high_earners AS (
    SELECT dept, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY dept
    HAVING AVG(salary) > 80000
),
recent_hires AS (
    SELECT * FROM employees WHERE hire_date > '2023-01-01'
)
SELECT r.name, r.salary, h.avg_sal
FROM recent_hires r
JOIN high_earners h ON r.dept = h.dept;

-- PostgreSQL / SQL Server / MySQL 8.0+
WITH RECURSIVE org_tree AS (
    -- Anchor: top-level managers
    SELECT id, name, manager_id, 0 AS level
    FROM employees WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: employees under each manager
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree;

┌──────────────────────────────────────────────────────────────────────────────┐
│                    GROUP BY vs WINDOW FUNCTION                               │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  Input:                              GROUP BY result:    Window result:      │
│  ┌──────┬────────┬────────┐          ┌──────┬────────┐   ┌──────┬────────┐   │
│  │ Dept │ Name   │ Salary │          │ Dept │ AVG    │   │ Name │ AVG    │   │
│  ├──────┼────────┼────────┤          ├──────┼────────┤   ├──────┼────────┤   │
│  │ A    │ Alice  │ 100    │    →     │ A    │ 150    │   │ Alice│ 150    │   │
│  │ A    │ Bob    │ 200    │          │ B    │ 400    │   │ Bob  │ 150    │   │
│  │ B    │ Carol  │ 400    │          └──────┴────────┘   │ Carol│ 400    │   │
│  └──────┴────────┴────────┘                              └──────┴────────┘   │
│                                                                              │
│  GROUP BY collapses rows            Window keeps all rows, adds calculation  │
│  (3 rows → 2 rows)                  (3 rows → 3 rows)                        │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘

-- ROW_NUMBER(): Unique rank, no ties
SELECT name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_num
FROM employees;
-- Alice: 50000 → 1, Bob: 50000 → 2, Carol: 40000 → 3

-- RANK(): Ties get same rank, next rank skips
SELECT name, salary,
    RANK() OVER (ORDER BY salary DESC) AS rank_num
FROM employees;
-- Alice: 50000 → 1, Bob: 50000 → 1, Carol: 40000 → 3

-- DENSE_RANK(): Ties get same rank, next rank doesn't skip
SELECT name, salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_num
FROM employees;
-- Alice: 50000 → 1, Bob: 50000 → 1, Carol: 40000 → 2

-- PARTITION BY: Reset window per group
SELECT dept, name, salary,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees;

-- LAG / LEAD: Previous / next row values
SELECT date, revenue,
    LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

-- Running total
SELECT name, salary,
    SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;

-- Moving average
SELECT date, temperature,
    AVG(temperature) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM weather;