Интенсив по SQL от Simulative

import pandas as pd
import sqlalchemy as sa
import sql

pd.set_option('display.max_rows', 10)

connection_url = sa.engine.URL.create(
    drivername="postgresql+psycopg2",
    host="95.163.241.236",
    port=5432,
    database="simulative",
    username="student",
    password="qweasd963",
)
engine = sa.create_engine(connection_url)

%load_ext sql

%config SqlMagic.displaycon = False
%config SqlMagic.autopandas = True

%sql engine

print(f"Pandas ver. {pd.__version__}: порог усечения строк уменьшен до 10")
print(f"SQLAlchemy ver. {sa.__version__}: подключение создано")
print(f"JupySQL ver. {sql.__version__}: подключен через SQLAlchemy Engine")

%%sql
SELECT * FROM users;

%%sql
SELECT
	u.username login,
	u.date_joined AS created_at
FROM users u;

%%sql
-- ASC сортировка по возрастанию (по умолчанию - можно не указывать)
SELECT
	id,
    username,
    first_name,
    last_name,
    date_joined
FROM users u
ORDER BY date_joined ASC;

%%sql
-- DESC сортировка по убыванию
SELECT
	id,
    username,
    first_name,
    last_name,
    date_joined
FROM users u
ORDER BY date_joined DESC;

%%sql
SELECT
	id,
    username,
    first_name,
    last_name,
    is_active
FROM users u
ORDER BY is_active;

%%sql
SELECT
	id,
    first_name,
    last_name,
    is_active,
    date_joined
FROM users u
ORDER BY is_active, date_joined DESC;

%%sql
SELECT 
	id,
	username,
	email,
	date_joined
FROM users
ORDER BY date_joined DESC, id;

%%sql
select
    id,
    username,
    email,
    date_joined
from users
order by date_joined desc, id;

%%sql
SELECT
    id,
    username,
    first_name,
    last_name,
    email
FROM users
WHERE id = 198;

%%sql
SELECT
    id,
    username,
    first_name,
    last_name,
    email
FROM users
WHERE 1 = 1;

%%sql
SELECT
    id,
    username,
    first_name,
    last_name,
    email
FROM users
WHERE True;

%%sql
SELECT
    id,
    username,
    first_name,
    last_name,
    is_active,
    referal_user,
    company_id,
    score
FROM users
--
WHERE id = 198 AND True;

%%sql
SELECT
    id,
    first_name,
    last_name,
    is_active,
    referal_user,
    company_id,
    score
FROM users
--
WHERE is_active  = 1 AND company_id IS NOT null;

%%sql
SELECT
    id,
    first_name,
    last_name,
    is_active,
    referal_user,
    company_id,
    score
FROM users
--
WHERE referal_user IS NOT NULL OR company_id IS NOT NULL;

%%sql
SELECT
    id,
    first_name,
    last_name,
    is_active,
    referal_user,
    company_id,
    score
FROM users
--
WHERE referal_user IS NOT NULL OR company_id IS NOT NULL OR score > 1000;

%%sql
SELECT
    id,
    first_name,
    last_name,
    is_active,
    referal_user,
    company_id,
    score
FROM users
--
WHERE referal_user IS NOT NULL OR company_id IS NOT NULL OR score > 500
ORDER BY score DESC;

%%sql
SELECT
    id,
    first_name,
    last_name,
    is_active,
    referal_user,
    company_id,
    score
FROM users
--
WHERE is_active + 1 = 2;

%%sql
SELECT id,
    first_name,
    last_name,
    is_active,
    referal_user,
    company_id,
    score
FROM users
--
WHERE 
	referal_user IS NOT NULL
	AND 
	company_id IS NOT NULL 
	OR 
	score > 500
ORDER BY score DESC;

%%sql
SELECT
    id,
    username,
    first_name,
    last_name,
    is_active,
    referal_user,
    company_id,
    score
FROM users
--
WHERE 
	referal_user IS NOT NULL
	AND
	(company_id IS NOT NULL
	OR
	score > 500)
ORDER BY score DESC;

%%sql
SELECT
    id,
    username,
    first_name,
    last_name,
    is_active,
    referal_user,
    company_id,
    score
FROM users
--
WHERE 
	referal_user IS NOT NULL
	OR 
	company_id IS NOT NULL 
	AND 
	score > 500
ORDER BY score DESC;

%%sql
SELECT
    id,
    username,
    first_name,
    last_name,
    is_active,
    referal_user,
    company_id,
    score
FROM users
--
WHERE 
	(referal_user IS NOT NULL
	OR  
	company_id IS NOT NULL)
	AND
	score > 500
ORDER BY score DESC;

%%sql
-- AND имеет приоритет над OR
-- Всегда расставляйте скобки
SELECT
    id,
    username,
    first_name,
    last_name,
    is_active,
    referal_user,
    company_id,
    score
FROM users
--
WHERE 
	(
		referal_user IS NOT NULL
	)
	OR  
	(
		company_id IS NOT NULL
		AND
		score > 500
	)
	OR
	(
		is_active = 0
	)
ORDER BY score DESC;

%%sql
SELECT
    id,
    username,
    first_name,
    last_name,
    is_active,
    referal_user,
    company_id,
    score
FROM users
--
WHERE company_id != 7; -- Пропали значения NULL

%%sql
SELECT
    id,
    first_name,
    last_name,
    is_active,
    referal_user,
    company_id,
    score
FROM users
--
WHERE company_id != 7 OR company_id IS NULL;

%%sql
SELECT
	is_active,
	is_active + 1 AS new_col
FROM users;

%%sql
SELECT
	is_active,
	is_active + 1 AS new_col
FROM users
WHERE new_col = 2;
-- ERROR мы не можем использовать алиасы из ТЕКУЩЕГО запроса в фильтрации

%%sql
SELECT
	is_active,
	is_active + 1 AS new_col
FROM users
WHERE is_active + 1 = 2;

%%sql
-- Практика 2. Задача 1
SELECT
	id,
	username,
	score
FROM users
WHERE
	score > 100 OR last_name IS NULL 
ORDER BY id;

%%sql
-- Практика 2. Задача 2
SELECT 
	id,
	username,
	company_id,
	score
FROM users
WHERE 
	is_active = 1
	AND 
	(score > 500 OR company_id = 7)
ORDER BY id;

%%sql
-- Задача из чата
SELECT * FROM users
ORDER BY first_name ASC, first_name DESC;

%%sql
SELECT LOWER('ПриВЕт');

%%sql
-- в двойных кавычках в PostgreSQL указываются объекты БД:
-- названия столбцов, названия таблиц - чтобы явно отличить от зарезервированных слов
SELECT 
	LOWER('ПриВЕт') AS "lower_case", -- в двойных кавычках
	'ПриВЕт' AS init_phrase;

%%sql
SELECT UPPER('ПриВЕт');

%%sql
SELECT
	name,
	LOWER(name),
	UPPER(name),
	INITCAP(name)
FROM problem;

%%sql
SELECT id, username, first_name, last_name,
    is_active, referal_user, company_id, score
FROM users
--
WHERE LOWER(username) = 'девелопер';

%%sql
SELECT
    id,
    name
FROM problem
--
WHERE lower(name) LIKE '%new%'; -- Регистрозависимый поиск

%%sql
SELECT
    id,
    name
FROM problem
--
WHERE name iLIKE '%new%'; -- регистроНЕзависимый поиск

%%sql
SELECT name, length(name)
FROM problem;

%%sql
SELECT email, right(email, 9)
FROM users;

%%sql
SELECT position('@' IN email)
FROM users;

%%sql
SELECT email, left(email, position('@' IN email))
FROM users;

%%sql
SELECT email, left(email, position('@' IN email) - 1)
FROM users;

%%sql
SELECT 
	email,
	left(email, position('@' IN email) - 1) AS left_part,
	right(email, length(email) - position('@' IN email)) AS right_part
FROM users;

%%sql
SELECT 
	id,
	username,
	first_name,
	last_name,
	LOWER(first_name) lower_first_name,
	UPPER(last_name) upper_last_name,
	length(username) length_username
FROM users
ORDER BY length_username DESC, id;

%%sql
SELECT 
	id,
	username,
	email,
	right(email, length(email) - position('@' IN email)) AS domain
FROM users
ORDER BY id;

%%sql
SELECT 
	id,
	username,
	email
FROM users
WHERE lower(email) LIKE '%@bk.ru'
ORDER BY id;

%%sql
WITH transaction as (
select 'Оплата за задержку' as description 
union all
select 'Платежка за жку, за октябрь 2025' as description
union all
select 'жку за октябрь 2025' as description
union all
select 'жкх' as description
union all
select 'оплата, жкх.' as description
union all
select 'ЖКУ' as description
union all
select 'жкх: оплата задежки'
union all
select 'Надо проверить платежку'
union all
select 'Оплата за (жку)'
    )
--
SELECT *
FROM transaction
WHERE INITCAP(description) LIKE '%Жку%'
OR INITCAP(description) LIKE '%Жкх%';

%%sql
-- Посмотрим на табл. users
SELECT
    id,
    username,
    first_name,
    last_name
FROM users;

%%sql
SELECT
    id,
    username,
    first_name,
    last_name,
    first_name || ' ' || last_name AS concat1, -- если есть NULL, то результат NULL
    CONCAT(first_name, ' ', last_name) concat2 -- если есть NULL, то результат строка
FROM users;

%%sql
SELECT
    id,
    first_name,
    last_name,
    first_name || ' ' || last_name AS concat1, -- если есть NULL, то результат NULL
    CONCAT(first_name, ' ', last_name) concat2, -- если есть NULL, то результат строка
    CONCAT_WS(' ', first_name, last_name) concat3 -- первым аргументом разделитель
FROM users;

%%sql
SELECT '' != NULL;

%%sql
SELECT '' IS NOT NULL;

%%sql
SELECT
    id,
    first_name,
    last_name,
    first_name || ' ' || last_name AS concat1,
        -- если есть NULL, то результат NULL
    CONCAT(first_name, ' ', last_name) concat2,
        -- если есть NULL, то результат строка
    CONCAT_WS(' ', first_name, last_name) concat3,
        -- первым аргументом разделитель
    COALESCE(first_name || ' ' || last_name, 'Дорогой клиент') concat4
        -- работает, т.к. `||` выдает явный NULL
FROM users;

%%sql
SELECT
    first_name,
    last_name,
    first_name || ' ' || last_name AS concat1,
    CONCAT(first_name, ' ', last_name) concat2,
    CONCAT_WS(' ', first_name, last_name) concat3,
    COALESCE(first_name || ' ' || last_name, 'Дорогой клиент') concat4,
        -- работает, т.к. `||` выдает явный NULL
    COALESCE(CONCAT(first_name, ' ', last_name), 'Дорогой клиент') concat5
        -- не корректно, т.к. `CONCAT` не выдает NULL (пустая строка IS NOT NULL)
FROM users;

%%sql
-- Посмотрим на вывод: как и ожидалось вместо NULL строка
SELECT CONCAT(first_name, ' ', last_name)
FROM users;

%%sql
-- Все значения где была пустая строка "обнулились"
SELECT NULLIF(CONCAT_WS(' ', first_name, last_name), '')
FROM users;

%%sql
-- Сравним вывод
SELECT 
    CONCAT_WS(' ', first_name, last_name),
    NULLIF(CONCAT_WS(' ', first_name, last_name), '')
FROM users;

%%sql
SELECT
 last_name,
 first_name || ' ' || last_name AS concat1,
 CONCAT(first_name, ' ', last_name) concat2,
 CONCAT_WS(' ', first_name, last_name) concat3,
 COALESCE(first_name || ' ' || last_name, 'Дорогой клиент') concat4,
 COALESCE(CONCAT(first_name, ' ', last_name), 'Дорогой клиент') concat5,
 COALESCE(NULLIF(CONCAT_WS(' ', first_name, last_name), ''), 'Дорогой клиент') concat6
      -- работает, т.к. NULLIF превращает пустую строку в NULL
FROM users;

%%sql
SELECT id, username, first_name, last_name, score,
    CASE 
        WHEN score < 100 THEN 'low'
        WHEN score < 500 THEN 'medium'
        ELSE 'high'
    END AS score2
FROM users
ORDER BY score2; -- чтобы увидеть разницу в усеченном выводе Pandas

%%sql
-- Вначале проверим что если имя NULL, то и фамилия NULL
-- Есть строки где задано только имя или только фамилия?

SELECT first_name, last_name
FROM users
WHERE first_name IS NULL AND last_name IS NOT NULL;
-- WHERE first_name IS NOT NULL AND last_name IS NULL

%%sql
SELECT
    first_name || ' ' || last_name AS concat1,
        -- если есть NULL, то результат NULL
    CONCAT(first_name, ' ', last_name) concat2,
        -- если есть NULL, то результат строка
    CONCAT_WS(' ', first_name, last_name) concat3,
        -- указываем первым аргументом разделитель
    COALESCE(first_name || ' ' || last_name, 'Дорогой клиент') concat4,
        -- работает, т.к. `||` выдает явный NULL
    COALESCE(CONCAT(first_name, ' ', last_name), 'Дорогой клиент') concat5,
        -- не корректно, т.к. `CONCAT` не выдает NULL (пустая строка IS NOT NULL)
    COALESCE(NULLIF(CONCAT_WS(' ', first_name, last_name), ''), 'Дорогой клиент') concat6,
        -- работает, т.к. NULLIF превращает пустую строку в NULL
    CASE
        WHEN first_name IS NULL THEN 'Дорогой клиент'
        ELSE CONCAT_WS(' ', first_name, last_name)
    END AS concat7
FROM users;

%%sql
SELECT 
	id,
	username,
	concat('Идентификатор пользователя равен ', id)
FROM users
ORDER BY id;

%%sql
SELECT 
	id,
	username,
	first_name,
	last_name,
	COALESCE(first_name, last_name, 'Дорогой друг') AS display_name
FROM users
ORDER BY id;

%%sql
SELECT
	id,
	username,
	score,
	CASE
		WHEN score > 300 THEN 'Мастер'
		WHEN score > 150 THEN 'Эксперт'
		WHEN score > 75 THEN 'Продвинутый'
		ELSE 'Новичок'
	END group_user
FROM users
ORDER BY id;

%%sql
SELECT *
FROM users;

%%sql
SELECT
    username,
    date_joined
FROM users;

%%sql
SELECT
    username,
    date_joined,
    TO_CHAR(date_joined, 'YYYY-MM') AS ym,
    TO_CHAR(date_joined, 'YYYY-MM-DD') AS ymd
FROM users;

%%sql
SELECT
    username,
    date_joined,
    TO_CHAR(date_joined, 'YYYY-MM') AS ym,
    TO_CHAR(date_joined, 'YYYY-MM-DD') AS ymd
FROM users
--
WHERE TO_CHAR(date_joined, 'YYYY-MM') = '2021-11';

%reload_ext sql

%%sql
SELECT
    username,
    date_joined,
    TO_CHAR(date_joined, 'YYYY-MM') AS ym,
    TO_CHAR(date_joined, 'YYYY-MM-DD') AS ymd,
    DATE_TRUNC('month', date_joined) AS ym2,
        -- отрежет все время и дату приведет на 1-й день месяца
    DATE_TRUNC('day', date_joined) AS ymd2,
    DATE_TRUNC('year', date_joined) AS "year"
FROM users;

%%sql
SELECT
    username,
    date_joined,
    MAKE_DATE(2037, 5, 5)
FROM users;

%%sql
SELECT
    username,
    date_joined,
    MAKE_DATE(2037, 5, 5) - date_joined AS diff_exact
        -- Разница времени между собранной датой и ..
FROM users;

%%sql
SELECT
    username,
    date_joined,
    MAKE_DATE(2037, 5, 5) - date_joined AS diff_exact,
    NOW() - date_joined AS diff_now
        -- Разница между текущей датой и определенной
FROM users;

%%sql
SELECT
    date_joined,
    MAKE_DATE(2037, 5, 5) - date_joined AS diff_exact,
    NOW() - date_joined AS diff_now,
    EXTRACT(days FROM MAKE_DATE(2037, 5, 5) - date_joined) AS diff_days
        -- Вытащи кол-во дней из разницы
FROM users;

%%sql
SELECT
    date_joined,
    MAKE_DATE(2037, 5, 5) - date_joined AS diff_exact,
    NOW() - date_joined AS diff_now,
    EXTRACT(days FROM MAKE_DATE(2037, 5, 5) - date_joined) AS diff_days,
    --
    EXTRACT(days FROM MAKE_DATE(2037, 5, 5) - date_joined) * 24 
    + EXTRACT(hours FROM MAKE_DATE(2037, 5, 5) - date_joined) 
    AS diff_hours
FROM users;

%%sql
SELECT
    date_joined,
    date_joined + INTERVAL '3 days' AS future_day,
    date_joined + INTERVAL '3 weeks' AS future_week,
    date_joined + INTERVAL '3 years' AS future_year
FROM users;

%reload_ext sql

%%sql
-- Практика 5. Задача 1
SELECT
	id,
	username,
	email,
	date_joined,
	date_trunc('month', date_joined) AS registration_month_start
FROM users
ORDER BY date_joined DESC, id;

%%sql
-- Практика 5. Задача 2
SELECT
	id,
	username,
	email,
	date_joined,
	TO_CHAR(date_joined, 'DD Mon YYYY') AS formatted_date
FROM users
ORDER BY date_joined DESC, id;

%%sql
-- Практика 5. Задача 3
SELECT
	id,
	username,
	email,
	date_joined,
	TO_CHAR(date_joined, 'YYYY-MM') AS formatted_date
FROM users
ORDER BY date_joined DESC, id;

%%sql
-- Практика 5. Задача 4
-- ВАРИАНТ 1: `MAKE_DATE`
SELECT
    id,
    username,
    date_joined
FROM users
WHERE date_joined BETWEEN MAKE_DATE(2022, 1, 1) 
                      AND MAKE_DATE(2022, 1, 1) + INTERVAL '45 days'
ORDER BY date_joined DESC, id;

%%sql
-- Практика 5. Задача 4
-- ВАРИАНТ 2: `DATE`
SELECT
	id,
	username,
	date_joined
FROM users
WHERE date_joined BETWEEN DATE '2022-01-01'
					  AND DATE '2022-01-01' + INTERVAL '45 days'
ORDER BY date_joined DESC, id;

%%sql
-- Практика 5. Задача 4
-- ВАРИАНТ 3: `CASE WHEN`
SELECT
    id,
    username,
    date_joined
FROM users
WHERE CASE
    WHEN date_joined < MAKE_DATE(2022, 1, 1) THEN 'no'
    WHEN date_joined > MAKE_DATE(2022, 1, 1) + INTERVAL '45 days' THEN 'no'
    ELSE 'yes'
END = 'yes'
ORDER BY date_joined DESC, id;

%%sql
-- Практика 5. Задача 5
SELECT
    id,
    username,
    date_joined
FROM users
WHERE TO_CHAR(date_joined, 'YYYY') = '2021'
ORDER BY id;

%%sql
-- Практика 5. Задача 6
SELECT
    id,
    username,
    email,
    date_joined
FROM users
-- 1-е место: идиоматичный ILIKE + быстрый числовой EXTRACT
WHERE email ILIKE '%@bk.ru'
  AND EXTRACT(year FROM date_joined) = 2022
/* Альтернативные варианты условий:
  -- 2-е место: через DATE_TRUNC и строгий литерал TIMESTAMP
  email ILIKE '%@bk.ru'
    AND DATE_TRUNC('year', date_joined) = TIMESTAMP '2022-01-01'

  -- 3-е место: через LOWER и строковое форматирование TO_CHAR
  LOWER(email) LIKE '%@bk.ru'
    AND TO_CHAR(date_joined, 'YYYY') = '2022'
*/
ORDER BY id;

%%sql
-- Практика 5. Задача 7
SELECT
    id,
    username,
    email,
    date_joined,
    score
FROM users
WHERE EXTRACT(year FROM date_joined) = 2021
    OR score > 100
ORDER BY id;

%%sql
-- Практика 5. Задача 8
SELECT
    id,
    username,
    email,
    date_joined
FROM users
WHERE (email iLIKE '%@bk.ru'
    OR email iLIKE '%@yandex.ru')
    AND EXTRACT(year FROM date_joined) = 2021
/*  AND DATE_TRUNC('year', date_joined) = TIMESTAMP '2021-01-01' */
ORDER BY id;

%%sql
SELECT
    score,
    score / 10
FROM users
WHERE score::text NOT LIKE '%0';
-- score::text превращает число в строку для LIKE (т.к. функции TEXT() нет)

%%sql
SELECT
    score,
    score / 10           AS integer_division,
    -- целочисленное деление (45 / 10 = 4)
    score / 10.0         AS divide_by_float,
    -- эталон: деление на литерал с точкой (4.5)
    score * 1.0 / 10     AS multiply_first
    -- повышение через умножение на 1.0
FROM users
WHERE score::text NOT LIKE '%0';

%%sql
SELECT
    score,
    score / 10           AS integer_division,
    score / 10.0         AS divide_by_float,
    score * 1.0 / 10     AS multiply_first,
    score::numeric / 10  AS explicit_cast,
    -- явное приведение типа (numeric)
    CAST(score AS numeric) / 10 AS ansi_cast_divide
    -- универсальный стандарт ANSI SQL для приведения типов
FROM users
WHERE score::text NOT LIKE '%0';

%%sql
SELECT
    score,
    score / 10        AS integer_division,
    CAST(score / 10.0 AS numeric(10, 2)) AS direct_cast_round
      -- СНАЧАЛА деление, а приведение к numeric(10, 2) ПОТОМ
FROM users
WHERE score::text NOT LIKE '%0';

%%sql
SELECT
    score,
    score / 10                            AS integer_division,
    ROUND(score / 10.0, 2)                AS divide_by_float,
    ROUND(score * 1.0 / 10, 2)            AS multiply_first,
    ROUND(score::numeric / 10, 2)         AS explicit_cast,
    ROUND(CAST(score AS numeric) / 10, 2) AS ansi_cast_divide
FROM users
WHERE score::text NOT LIKE '%0';

%%sql
SELECT
    DISTINCT RIGHT(email, LENGTH(email) - POSITION('@' IN email)) AS unic
FROM users
ORDER BY unic;

%%sql
SELECT *
FROM coderun c

%%sql
SELECT *
FROM coderun c
JOIN users u
ON c.user_id = u.id;

%%sql
SELECT c.*, u.username
FROM coderun c
JOIN users u
ON c.user_id = u.id;

%%sql
SELECT *
FROM coderun c
RIGHT JOIN users u
ON c.user_id = u.id;

%%sql
SELECT *
FROM coderun c
RIGHT JOIN users u
ON c.user_id = u.id
WHERE c.id IS NULL;

%%sql
SELECT *
FROM users u
LEFT JOIN coderun c
ON u.id = c.user_id
WHERE c.id IS NULL;

%%sql
SELECT *
FROM coderun c
JOIN users u
ON c.user_id = u.id
JOIN problem p
ON p.id = c.problem_id;

%%sql
-- Очень аккуратно с JOIN'ами
SELECT *
FROM coderun c
RIGHT JOIN users u
ON c.user_id = u.id
JOIN problem p
ON p.id = c.problem_id
WHERE c.problem_id IS NULL;

%%sql
-- Правильно
SELECT *
FROM coderun c
RIGHT JOIN users u
ON c.user_id = u.id
LEFT JOIN problem p
ON p.id = c.problem_id
WHERE c.problem_id IS NULL;

%%sql
SELECT u.company_id
FROM users u;

%%sql
SELECT *
FROM company c;

%%sql
SELECT *
FROM coderun c
RIGHT JOIN users u
ON c.user_id = u.id
JOIN company c2
ON COALESCE(u.company_id, 1) = c2.id;

%%sql
SELECT COUNT(*)
FROM coderun c
RIGHT JOIN users u
ON c.user_id = u.id
JOIN problem p
ON p.id = c.problem_id;

%%sql
SELECT COUNT(*)
FROM coderun c
RIGHT JOIN users u
ON c.user_id = u.id
LEFT JOIN problem p
ON p.id = c.problem_id;

%%sql
SELECT count(*)
FROM coderun c
RIGHT JOIN users u
ON c.user_id = u.id
JOIN company c2
ON COALESCE(u.company_id, 1) = c2.id;

%%sql
SELECT count(*)
FROM coderun c
RIGHT JOIN users u
ON c.user_id = u.id
LEFT JOIN company c2
ON COALESCE(u.company_id, 1) = c2.id;

%%sql
SELECT *
FROM coderun c
JOIN users u
ON c.user_id = u.id AND created_at <= date_joined;

%%sql
-- Эквивалентно предыдущему
SELECT *
FROM coderun c
JOIN users u
ON c.user_id = u.id
WHERE created_at <= date_joined;

%%sql
-- Заменим на RIGTH JOIN
SELECT *
FROM coderun c
RIGHT JOIN users u
ON c.user_id = u.id AND created_at <= date_joined;

%%sql
-- Пример использования OR
SELECT *
FROM coderun c
JOIN users u
ON (c.user_id = u.id AND created_at < date_joined)
    OR (c.user_id = u.id AND created_at = date_joined);

%%sql
SELECT *
FROM problem_to_company ptc;

%%sql
SELECT *
FROM users u
JOIN company c
ON u.company_id = c.id
WHERE username = 'nsosabisfsobm';

%%sql
SELECT *
FROM problem p
JOIN problem_to_company ptc 
ON p.id = ptc.problem_id AND ptc.company_id = 1
WHERE p.id = 144;