-- Способ 1: OFFSET
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

-- Способ 2: DENSE_RANK (учитывает дубликаты)
SELECT salary FROM (
   SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
   FROM employees
) t WHERE rnk = 2;

-- Для N = 5:
SELECT salary FROM (
   SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
   FROM employees
) t WHERE rnk = 5;

SELECT * FROM (
   SELECT
       e.*,
       d.name AS department_name,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
   FROM employees e
   JOIN departments d ON e.department_id = d.id
) ranked
WHERE rn <= 3;

-- С DENSE_RANK (если нужны все с топ-3 зарплатами):
SELECT * FROM (
   SELECT e.*, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
   FROM employees e
) ranked WHERE rnk <= 3;

SELECT
   e.name,
   SUM(o.amount) AS total_sales,
   RANK() OVER (ORDER BY SUM(o.amount) DESC) AS sales_rank
FROM employees e
JOIN orders o ON e.id = o.employee_id
GROUP BY e.id, e.name
ORDER BY sales_rank;

SELECT
   day::date,
   SUM(daily_total) OVER (ORDER BY day) AS running_total
FROM (
   SELECT
       date_trunc('day', order_date) AS day,
       SUM(amount) AS daily_total
   FROM orders
   GROUP BY date_trunc('day', order_date)
) daily;

SELECT * FROM (
   SELECT
       e.*,
       d.name AS department_name,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
   FROM employees e
   JOIN departments d ON e.department_id = d.id
) ranked
WHERE rn <= 3;

-- С DENSE_RANK (если нужны все с топ-3 зарплатами):
SELECT * FROM (
   SELECT e.*, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
   FROM employees e
) ranked WHERE rnk <= 3;

SELECT
   d.name AS department,
   COUNT(*) AS emp_count,
   ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY pct DESC;

SELECT
   customer_id,
   order_date,
   amount,
   LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
   amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS diff
FROM orders;

SELECT
   customer_id,
   order_date,
   amount,
   LAG(amount) OVER w AS prev_amount,
   ROUND(100.0 * (amount - LAG(amount) OVER w) / NULLIF(LAG(amount) OVER w, 0), 2) AS pct_change
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date);

SELECT
   customer_id,
   order_date,
   LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order,
   order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_between
FROM orders;

-- ❌ НЕПРАВИЛЬНО: Это среднее по 3 ЗАПИСЯМ, не по 3 ДНЯМ!
SELECT
   order_date::date AS day,
   SUM(amount) AS daily_total,
   AVG(SUM(amount)) OVER (
       ORDER BY order_date::date
       ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
   ) AS moving_avg_3_rows  -- Это НЕ 3 дня!
FROM orders
GROUP BY order_date::date;

-- ✅ ПРАВИЛЬНО: Заполняем пропуски через generate_series
WITH all_days AS (
   SELECT generate_series(
       (SELECT MIN(order_date::date) FROM orders),
       (SELECT MAX(order_date::date) FROM orders),
       '1 day'::interval
   )::date AS day
),
daily_totals AS (
   SELECT order_date::date AS day, SUM(amount) AS daily_total
   FROM orders
   GROUP BY order_date::date
)
SELECT
   d.day,
   COALESCE(t.daily_total, 0) AS daily_total,
   AVG(COALESCE(t.daily_total, 0)) OVER (
       ORDER BY d.day
       ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
   ) AS moving_avg_3_days
FROM all_days d
LEFT JOIN daily_totals t ON d.day = t.day
ORDER BY d.day;

-- Идея: если вычесть номер строки из даты, последовательные дни дадут одинаковый grp
WITH unique_days AS (
   SELECT DISTINCT user_id, activity_date::date AS day
   FROM user_activity
),
numbered AS (
   SELECT
       user_id,
       day,
       day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
   FROM unique_days
)
SELECT
   user_id,
   MIN(day) AS streak_start,
   MAX(day) AS streak_end,
   COUNT(*) AS streak_length
FROM numbered
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY streak_length DESC, user_id;

WITH ordered AS (
   SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id
   FROM orders
)
SELECT id + 1 AS gap_start, next_id - 1 AS gap_end
FROM ordered
WHERE next_id - id > 1;

INSERT INTO users (name, email, age, city)
VALUES ('Иван Петров', 'ivan@mail.ru', 30, 'Москва');

INSERT INTO products (name, category, price, stock) VALUES
   ('Телефон', 'Электроника', 50000, 100),
   ('Ноутбук', 'Электроника', 80000, 50),
   ('Футболка', 'Одежда', 2000, 200);

UPDATE products
SET price = price * 1.10
WHERE category = 'Электроника';

UPDATE employees e
SET salary = salary * 1.10
FROM departments d
WHERE e.department_id = d.id AND d.name = 'IT';

DELETE FROM orders
WHERE user_id IN (
   SELECT id FROM users WHERE status = 'blocked'
);

-- Или с USING:
DELETE FROM orders o
USING users u
WHERE o.user_id = u.id AND u.status = 'blocked';

INSERT INTO products (name, category, price, stock)
VALUES ('Phone', 'Электроника', 50000, 100)
ON CONFLICT (name) DO UPDATE SET
   price = EXCLUDED.price,
   stock = products.stock + EXCLUDED.stock;

-- Примечание: для работы ON CONFLICT нужен UNIQUE constraint на name:
-- ALTER TABLE products ADD CONSTRAINT uq_products_name UNIQUE (name);

-- Вместо DELETE:
UPDATE users SET deleted_at = NOW() WHERE id = 123;

-- VIEW для активных:
CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;

CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE LOWER(email) = 'test@test.com';

CREATE INDEX idx_email_lower ON users(LOWER(email));

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON users USING gin(name gin_trgm_ops);

SELECT * FROM users WHERE name LIKE '%John%';  -- теперь быстро

SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending' ORDER BY order_date DESC;

-- Составной индекс в правильном порядке:
CREATE INDEX idx_orders ON orders(customer_id, status, order_date DESC);

-- Или покрывающий:
CREATE INDEX idx_orders ON orders(customer_id, status, order_date DESC) INCLUDE (amount);

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- Создать функциональный индекс:
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Или нормализовать данные (триггер):
CREATE FUNCTION normalize_email() RETURNS TRIGGER AS $$
BEGIN
   NEW.email = LOWER(NEW.email);
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_normalize_email
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION normalize_email();

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- Первая страница:
SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20;

-- Следующие страницы (передаём последние значения):
SELECT * FROM orders
WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Индекс:
CREATE INDEX idx_orders_pagination ON orders(created_at DESC, id DESC);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;

-- Результат:
-- Seq Scan on orders  (cost=0.00..1520.00 rows=50) (actual time=0.020..15.432 rows=47 loops=1)
--   Filter: (customer_id = 123)
--   Rows Removed by Filter: 9953

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN ANALYZE
SELECT customer_id, amount FROM orders WHERE customer_id = 123;

-- Index Scan using idx_orders_customer on orders
--   Heap Fetches: 47

CREATE INDEX idx_orders_covering ON orders(customer_id) INCLUDE (amount);

-- Теперь будет:
-- Index Only Scan using idx_orders_covering
--   Heap Fetches: 0  -- не обращается к таблице!

-- Nested Loop  (actual time=0.050..45000.000 rows=1000 loops=1)
--   ->  Seq Scan on orders o  (actual time=0.010..10.000 rows=1000 loops=1)
--   ->  Index Scan on users u  (actual time=0.040..44.000 rows=1 loops=1000)

-- Проверить статистику:
ANALYZE orders;
ANALYZE users;

-- Увеличить work_mem для Hash Join:
SET work_mem = '256MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders ORDER BY amount DESC LIMIT 100;

-- Sort Method: external merge  Disk: 15000kB

SET work_mem = '64MB';
-- Sort Method: quicksort  Memory: 25000kB

CREATE INDEX idx_orders_amount ON orders(amount DESC);
-- Index Scan Backward — вообще без Sort!

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');

-- Bitmap Heap Scan on orders
--   Recheck Cond: (status = ANY (...))
--   Heap Blocks: exact=2000
--   ->  Bitmap Index Scan on idx_orders_status

SET work_mem = '64MB';  -- больше памяти для bitmap

WITH all_days AS (
   SELECT generate_series(
       '2024-01-01'::date,
       '2024-01-31'::date,
       '1 day'::interval
   )::date AS day
),
daily_sales AS (
   SELECT DATE(created_at) AS day, SUM(amount) AS total, COUNT(*) AS orders
   FROM orders
   WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'
   GROUP BY DATE(created_at)
)
SELECT
   d.day,
   TO_CHAR(d.day, 'Day') AS day_name,
   COALESCE(s.orders, 0) AS orders,
   COALESCE(s.total, 0) AS total
FROM all_days d
LEFT JOIN daily_sales s ON d.day = s.day
ORDER BY d.day;

WITH all_days AS (
   SELECT generate_series(
       CURRENT_DATE - INTERVAL '30 days',
       CURRENT_DATE,
       '1 day'::interval
   )::date AS day
)
SELECT d.day
FROM all_days d
LEFT JOIN orders o ON DATE(o.created_at) = d.day
WHERE o.id IS NULL
ORDER BY d.day;