| customer_id | quarter | order_amount |
|-------------|---------|--------------|
| 101         | Q1      | 5000         |
| 101         | Q2      | 7000         |
| 101         | Q3      | 6000         |
| 102         | Q1      | 3000         |
| 102         | Q2      | 4500         |
| 102         | Q4      | 5500         |

SELECT  
  customer_id,  
  SUM(CASE WHEN quarter = 'Q1' THEN order_amount ELSE 0 END) AS q1_sales,  
  SUM(CASE WHEN quarter = 'Q2' THEN order_amount ELSE 0 END) AS q2_sales,  
  SUM(CASE WHEN quarter = 'Q3' THEN order_amount ELSE 0 END) AS q3_sales,  
  SUM(CASE WHEN quarter = 'Q4' THEN order_amount ELSE 0 END) AS q4_sales  
FROM Orders  
GROUP BY customer_id  
ORDER BY customer_id;  

| customer_id | q1_sales | q2_sales | q3_sales | q4_sales |
|-------------|----------|----------|----------|----------|
| 101         | 5000     | 7000     | 6000     | 0        |
| 102         | 3000     | 4500     | 0        | 5500     |

employee_id | salary
------------|----------
1           | 100000
2           | 150000
3           | 130000
4           | 120000
5           | 1000000
6           | 110000

WITH ranked AS (
    SELECT 
        salary,
        ROW_NUMBER() OVER (ORDER BY salary) AS rn,
        COUNT(*) OVER () AS total_count
    FROM salaries
)
SELECT AVG(salary) AS median
FROM ranked
WHERE rn IN (
    FLOOR((total_count + 1) / 2.0), 
    CEIL((total_count + 1) / 2.0)
);

salary  | rn | total_count
--------|----|-----------
100000  | 1  | 6
110000  | 2  | 6
120000  | 3  | 6
130000  | 4  | 6
150000  | 5  | 6
1000000 | 6  | 6

FLOOR((total_count + 1) / 2.0) = FLOOR((6 + 1) / 2.0)  = FLOOR(3.5) = 3 (округление вниз)

CEIL((total_count + 1) / 2.0) = CEIL((6 + 1) / 2.0)  = CEIL(3.5) = 4 (округление вверх)

salary  | rn
--------|----
120000  | 3  ← берём
130000  | 4  ← берём

Результат:
----------------------------------------------------------------------
median = AVG(120000, 130000) = (120000 + 130000) / 2 = 250000 / 2 = 125000

SELECT 
    employee_id,
    employee_name,
    department_name,
    salary
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

SELECT 
    e.employee_id,
    e.employee_name,
    d.department_name,
    e.salary
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.department_id;

def calculate_conversion(users, converted):
    return converted / users

def calculate_conversion(users: int, converted: int) -> float:
    """
    Рассчитывает конверсию пользователей.
    
    Args:
        users (int): Общее количество пользователей (должно быть > 0).
        converted (int): Количество конвертированных пользователей.
    
    Returns:
        float: Конверсия в долях (например, 0.05 для 5%).
    
    Raises:
        ValueError: Если users <= 0.
    
    Example:
        >>> calculate_conversion(1000, 50)
        0.05
    """
    if users <= 0:
        raise ValueError("Количество пользователей должно быть больше 0")
    
    return converted / users

SELECT
    order_id,
    amount
FROM orders
WHERE
    1=1
    AND amount > 1000  -- опечатка: amount, не mount
    AND order_date >= '2025-01-01'
    AND status = 'completed';

SELECT
    order_id,
    amount
FROM orders
WHERE
    1=1
    -- AND amount > 1000  -- убрали, и всё ок
    AND order_date >= '2025-01-01'
    AND status = 'completed';

SELECT
    order_id
  , customer_id
  , order_date
  , amount
FROM orders
WHERE 1=1
  AND order_date >= '2025-01-01';

SELECT
    order_id
    -- , customer_id  -- закомментировали, запятая ушла
  , order_date
  , amount
FROM orders
WHERE 1=1
  AND order_date >= '2025-01-01';

| tx_date    | value |
|------------|-------|
| 2025-10-01 | 2     |
| 2025-10-01 | 3     |
| 2025-10-01 | 5     |
| 2025-10-02 | 4     |
| 2025-10-02 | 2     |

SELECT
    tx_date,
    EXP(SUM(LN(value))) AS product_value
FROM transactions
GROUP BY tx_date
ORDER BY tx_date;

SELECT order_date, customer_city, COUNT(*)
FROM orders
GROUP BY order_date, customer_city

SELECT order_date, customer_city, COUNT(*)
FROM orders
GROUP BY 1, 2  -- 1 и 2 — это первый и второй столбцы в SELECT

SELECT
    category,
    SUM(sales) AS total_sales,
    SUM(sales) * 100.0 / SUM(all_sales) AS percent -- 100.0 заставит SQL считать дроби
FROM sales
GROUP BY category

SELECT
    category,
    SUM(sales) AS total_sales,
    CAST(SUM(sales) * 100 AS DECIMAL(5,2)) / SUM(all_sales) AS percent
FROM sales
GROUP BY category

connections.py

# connections.py
pg_url = 'postgresql://user:pass@old_host/db'

def connect_db():
    import psycopg2
    return psycopg2.connect(pg_url)

connections.py

import sys
sys.path.append('/home/dima_sqlit/modules')  # Ваш путь к общей папке

from connections import connect_db

conn = connect_db()
cursor = conn.cursor()
cursor.execute("SELECT * FROM data LIMIT 10")
data = cursor.fetchall()  # Готово для анализа!