Slava Rozhnev

SELECT 
	device_name,
	COUNT(*) cnt
FROM users
WHERE 
	YEAR(created_date) = 2020 AND
	year_of_birth < DATE_SUB(CURDATE(), INTERVAL 25 YEAR)
GROUP BY device_name;

SELECT * 
FROM comment
WHERE (id = 1 AND reply_id IS NULL) OR reply_id = 1;

CREATE TABLE `textlog` (
    `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `chat_id` int(111) NOT NULL,
    `text` text NOT NULL,
    `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP   
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

UPDATE tbl 
SET link = CONCAT(
  '<p>[playerjs file:\"', 
  MID(LEFT(link, LOCATE('?', link, LOCATE('src="', link))-1), LOCATE('src="', link)+5),
  '\"]</p>'
);

select * 
from messages 
join (
  select 
        -- создаем JSON ARRAY из id и берем 2 последних элемента
	IF (
	  JSON_LENGTH(json_arrayagg(id))>2,
	  JSON_EXTRACT(json_arrayagg(id), CONCAT("$[",JSON_LENGTH(json_arrayagg(id))-2,"]"), CONCAT("$[",JSON_LENGTH(json_arrayagg(id))-1,"]")),
	  json_arrayagg(id)
	) ids,
	group_id
  from 
	messages
  group by group_id
) last_messages on json_contains(ids, CAST(messages.id AS JSON))
;

select posts.id, posts.body, sum(
  case direction 
  	when 'UP' then 1
  	when 'DOWN' then -1
  	else 0
  end) likes
from posts 
left join likes on likes.post_id = posts.id
group by posts.id, posts.body
order by likes desc
;

select posts.id, posts.body, (
  count(nullif(direction, 'DOWN')) - count(nullif(direction, 'UP'))) likes
from posts 
left join likes on likes.post_id = posts.id
group by posts.id, posts.body
order by likes desc
;

select * from tbl 
	where from_id + to_id = 20 + 32 and abs(from_id - to_id) = abs(20 - 32);

select tbl1.* 
from tbl tbl1
join tbl tbl2 on tbl1.num1 = tbl2.num2 and tbl1.num2 = tbl2.num1
where tbl2.id < tbl1.id
;

+====+======+======+
| id | num1 | num2 |
+====+======+======+
| 6  | 30   | 10   |
+----+------+------+

SELECT `title` AS 'Заголовок статьи', `article`.`id` AS 'Id статьи' 
FROM `article`
LEFT JOIN `photo` ON `photo`.`article_id` = `article`.`id` AND `photo`.`made` = 'before' -- or 'after'
WHERE `article_id` IS NULL
GROUP BY `article`.`id`

SELECT `title` AS 'Заголовок статьи', `article`.`id` AS 'Id статьи' 
FROM `article`
WHERE NOT EXISTS (
    SELECT `article_id` 
    FROM `photo` 
    WHERE `photo`.`article_id` = `article`.`id` AND `photo`.`made` = 'before' -- or 'after'
);

select 
	users.id,
	users.name,
	sum(money.value) as user_value
from users
join money on users.id  = money.user_id
where value > 0
group by users.id, users.name
order by user_value desc;