Akina

Подобный текст в вакансиях обычно означает вовсе даже не сложные запросы. Обычно речь о сильнозамороченных запросах, которые полны текста и со стороны смотрятся лихозакрученным многоэтажным безобразием, но на самом деле простые и плоские как блин. Тупая выборка, тупой подзапрос, тупые вычисления... всё, что надо - это аккуратность и способность донести понимание, что есть что и зачем делается, не расплескав, до конца запроса. Ибо если потерял мысль на полпути - проще стереть и начать заново, чем вспоминать, что это и нахрена ты это тут сделал.

Сложные, реально сложные, запросы бывают (по крайней мере навскидку) двух типов.

Первый - это запросы, которые имеющимися (как правило в конкретной СУБД либо в конкретной версии) средствами должны выполнить то, что в данной версии не имплементировано. Типичнейший пример из этой категории - например, динамические сводные запросы в большинстве СУБД. Или эмуляция рекурсивных запросов и оконных функций там, где их нет.

Второй - это запросы, в которых для оптимизации скорости выполнения приходится далеко отходить от прямого, "в лоб", выполнения, и строить достаточно замороченные тексты, которые реализуют ту же логику, но из-за особенностей выполнения запроса сервером гораздо более эффективны. Тут, пожалуй, типичный запрос привести трудно, но достаточно характерным примером может служить выбор между WHERE [NOT] EXISTS и LEFT JOIN WHERE IS [NOT] NULL (и обязательным гноблением WHERE [NOT] IN).

Список требуемых id должен быть источником данных запроса, причём базовым. И при возможном отсутствии связанных записей связывание должно быть внешнее.

SELECT service.id AS service_id, COUNT(subs.id) AS c
FROM ( SELECT 13 id UNION 
       SELECT 74    UNION
       SELECT 71    UNION
       SELECT 72 ) AS service
LEFT JOIN Subscriptions subs ON subs.service_id = service.id AND subs.msisdn=992777757031
GROUP BY service.id
ORDER BY c DESC;

WITH 
cte1 AS ( SELECT id, name, ROW_NUMBER() OVER (ORDER BY name) rn
          FROM t1 ),
cte2 AS ( SELECT id, name, ROW_NUMBER() OVER (ORDER BY name) rn
          FROM t2 )
SELECT cte1.id id1, cte1.name name1, cte2.id id2, cte2.name name2
FROM cte1
JOIN cte2 USING (rn, id);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b5a064...

Хотя, конечно, бред рафинированный. С точки зрения логики.

SELECT *
FROM tablename
WHERE datecolumn <= CURRENT_DATE
ORDER BY datecolumn DESC 
FETCH FIRST 1 ROWS WITH TIES

Порядок кляуз в запросе чётко определён в документации, и никаких изменений и перестановок не предполагает (исключение - кляуза INTO при помещении результата в переменную, для которой описано 3 разных местоположения).

SELECT u.*, t.type_name, t.description 
FROM users AS u 
LEFT JOIN type_user AS tu ON tu.id_user = u.id 
LEFT JOIN types AS t ON tu.id_type = t.id
WHERE u.id = 1;

WITH RECURSIVE
cte1 AS ( SELECT MIN(DATE(create_date)) mindate, MAX(DATE(create_date)) maxdate
          FROM shops
          UNION ALL
          SELECT MIN(DATE(create_date)) mindate, MAX(DATE(create_date)) maxdate
          FROM users ),
cte2 AS ( SELECT MIN(mindate) mindate, MAX(maxdate) maxdate
          FROM cte1 ),
dates AS ( SELECT mindate thedate, maxdate
           FROM cte2
           UNION ALL
           SELECT thedate + INTERVAL 1 DAY, maxdate
           FROM dates
           WHERE thedate < maxdate ),
shopstat AS ( SELECT DATE(create_date) thedate, COUNT(*) cnt
              FROM shops
              GROUP BY thedate ),
userstat AS ( SELECT DATE(create_date) thedate, COUNT(*) cnt
              FROM users
              GROUP BY thedate )
SELECT thedate `date`, 
       COALESCE(shopstat.cnt, 0) shops, 
       COALESCE(userstat.cnt, 0) users
FROM dates
LEFT JOIN shopstat USING (thedate)
LEFT JOIN userstat USING (thedate)

Если нужны данные за определённый период - убрать cte1 и cte2, использовать границы диапазона в dates (поле maxdate - не нужно, убрать), добавить соотв. WHERE в остальные CTE.

Алиас выходного поля нельзя использовать во WHERE.

Правильно - использовать копию вычисляющего значения выражения:

SELECT event, 
       CONVERT_TZ(time, '+00:00', '-04:00') AS time1 
FROM events 
WHERE CONVERT_TZ(time, '+00:00', '-04:00') between '2021-06-24 00:00:00' AND '2021-06-24 23:59:59' 
  AND lang = 'ru'

Расширение MySQL также допускает использовать алиас выходного поля в HAVING (при условии что в выражении не используются оконные функции):

SELECT event, 
       CONVERT_TZ(time, '+00:00', '-04:00') AS time1 
FROM events 
WHERE lang = 'ru'
HAVING time1 between '2021-06-24 00:00:00' AND '2021-06-24 23:59:59'

WHERE created_at BETWEEN CURRENT_TIMESTAMP - INTERVAL 1 HOUR
                     AND CURRENT_TIMESTAMP - INTERVAL 30 MINUTE

SELECT main.id,
       ( SELECT COUNT(*) 
         FROM news 
         WHERE main.id = news.main_id ) news,
       ( SELECT COUNT(*) 
         FROM articles
         WHERE main.id = articles.main_id ) articles
FROM main
ORDER BY news DESC;

Предполагается, что main(id) есть уникальное поле (скорее всего первичный ключ).

Вывод дубликатов:

SELECT t1.*
FROM table t1
WHERE EXISTS ( SELECT NULL
               FROM table t2
               WHERE t1.id <> t2.id -- выражение первичного ключа
                 AND t1.column = t2.column -- для всех полей, кроме первичного ключа

Удаление:

DELETE 
FROM table t1
WHERE EXISTS ( SELECT NULL
               FROM table t2
               WHERE t1.id > t2.id -- оставить только запись с минимальным ID
                 AND t1.column = t2.column