select u.name, sum(amount)
from account a
join user u on u.id = a.user_id
join operation o on o.account_id = a.id
where a.currency = 'EUR'
group by u.id, u.name
having sum(amount) > (
select avg(amount)
from account a
join operation o on o.account_id = a.id
where a.currency = 'EUR'
)
select FORMAT(dt, 'yyyy-MM-dd') as d, max(t) as t
from T
group by FORMAT(dt, 'yyyy-MM-dd');
select max_t.*, FORMAT(T.dt, 'HH:mm') hour
from (
select FORMAT(dt, 'yyyy-MM-dd') as date, max(t) as max_t
from T
group by FORMAT(dt, 'yyyy-MM-dd')
) max_t
join T on FORMAT(T.dt, 'yyyy-MM-dd') = max_t.date and T.t = max_t.max_t;
select name, avg(rate) rate
from users u
left join replies r on r.user_id = u.id
group by u.name
order by rate desc
$res = array_reduce(
$data,
function($res, $el) {
// Проверяет, есть ли пара имени бренда в массиве результатов
if (!isset($res[$el['name'].$el['brand']])) {
$res[$el['name'].$el['brand']] = $el;
} elseif (
// если пара имени бренда в массиве результатов проверяет цену
$res[$el['name'].$el['brand']]['price'] > $el['price']
) {
$res[$el['name'].$el['brand']]['price'] = $el['price'];
}
return $res;
},
[]
);
select * from clc
where not exists (select user_id from stat where stat.user_id = clc.user_id);
select clc.*
from clc
left join stat on stat.user_id = clc.user_id
where stat.user_id is null;
SELECT
`tokens`.*
FROM `tokens`
ORDER BY REPLACE(SUBSTRING_INDEX(s_short,';',3), SUBSTRING_INDEX(s_short,';',2), '') DESC;
SELECT
`tokens`.*
FROM `tokens`
ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(s_short,';',3),';',-1) DESC;
usort(
$arr,
fn($a, $b) => array_search($a['id'], $ids) <=> array_search($b['id'], $ids)
);
concat('category_id=', t1.id) = t2.category_id;
SELECT posts.id_post, posts.text_post, posts.photo, posts.created_at,
users.id_user, users.login, users.name, users.surname, users.avatar
COALESCE(COUNT(`likes`.`id_post`), 0) AS `likes`
FROM `posts`
INNER JOIN `users_connections` ON posts.id_user = users_connections.id_followed
INNER JOIN `users` ON users.id_user = posts.id_user
LEFT JOIN `likes` ON `likes`.`id_post` = `posts`.`id_post`
WHERE users_connections.id_follower = :id_follower
AND `created_at` BETWEEN '$prewweek' AND '$nowdate' ORDER BY `created_at`
GROUP BY posts.id_post, posts.text_post, posts.photo, posts.created_at,
users.id_user, users.login, users.name, users.surname, users.avatar
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
manager_id INT REFERENCES employees(id),
full_name VARCHAR,
address VARCHAR,
phone VARCHAR,
hired_at TIMESTAMP,
fired_at TIMESTAMP
);
CREATE TABLE order_states (
id SERIAL PRIMARY KEY,
state VARCHAR
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer VARCHAR,
address VARCHAR,
courier_id INT REFERENCES employees(id),
price NUMERIC
);
CREATE TABLE trace (
id BIGSERIAL PRIMARY KEY,
state_id INT REFERENCES order_states(id),
order_id INT REFERENCES orders(id),
updated_at TIMESTAMP
);
CREATE INDEX idx_employees_manager_id ON employees(manager_id);
CREATE INDEX idx_orders_courier_id ON orders(courier_id);
CREATE INDEX idx_trace_state_id_order_id ON trace(state_id, order_id);
CREATE TABLE book (
id INTEGER PRIMARY KEY,
title VARCHAR(128),
year INTEGER,
author_id INTEGER,
FOREIGN KEY(author_id) REFERENCES author(id)
);