nozzy

select 
t1.id,
t1.quest_id,
t1.point_id,
t1.created
from quests_prices t1
inner join 
(
 select
 quest_id,
 point_id,
 MAX(created) max_created
 from quests_prices 
 group by quest_id, point_id
) t2 on t2.quest_id = t1.quest_id 
  and t2.point_id = t1.point_id 
  and t2.max_created = t1.created

select
t2.epoch,
t2.`value`
from
(
 select 
 t1.`value`,
 t1.epoch,
 (select `value` from data where epoch < t1.epoch ORDER BY epoch DESC LIMIT 1) prev_value,
 (select `value` from data where epoch > t1.epoch ORDER BY epoch LIMIT 1) next_value
 from data t1
 where t1.value = 0
 order by t1.epoch
) t2
where t2.prev_value = 0 and t2.next_value = 0

SELECT t1.id AS company_id,
(SELECT @lists_id := GROUP_CONCAT(id) from lists_prod where campaign_id = t1.id),

(SELECT COUNT(*)
  FROM `sent`
        WHERE `list_id` IN (@lists_id)
       ) AS count
       
      FROM companies t1 LIMIT 100;

select
t3.Continent,
t3.Region,
sum(t3.Countries),
sum(t3.LifeDuration),
sum(t3.Population),
sum(t3.Cities),
sum(t4.Languages)
from
(
select
  t1.Code,
  t1.Continent,
  t1.Region,
  t1.Countries,
  t1.LifeDuration,
  t1.Population,
  t2.Cities
  from
   ( 
     select
     Code,
     Continent,
     Region,
     count(Name) as Countries,
     ROUND(AVG(LifeExpectancy), 2) as LifeDuration, 
     SUM(Population) as Population
     from country
     group by Code,Continent,Region
) t1 left join 
(
  select
  CountryCode,
  count(Name) as Cities
  from city
  group by CountryCode 
) t2 on t2.CountryCode = t1.Code
) t3 left join 
(
  select 
  CountryCode,
  count(Language) as Languages
  from countrylanguage
) t4 on t4.CountryCode = t3.Code

group by t3.Continent,t3.Region

select 
p.id,
t.tags,
t.count_tags
from Posts p
inner join 
(
  select 
  posts,
  tags,
  count(tags) as count_tags
  from Posts_Tags 
  group by posts, tags	
) t on t.posts = p.id
where p.date = (за последний час)
order by t.count_tags, p.id

select 
t1.id,
t2.name
from 
shop_products as t1
inner join shop_category_i18n as t2 on t2.id = t1.category_id

SELECT
t1.YEAR,
t1.MONTH,
((t1.REGS - t2.REGS) / t1.REGS) * 100 as DYNAMIC
FROM
(
SELECT
  EXTRACT(YEAR FROM created_at) as YEAR,
  EXTRACT(MONTH FROM created_at) as MONTH,
  COUNT(*) AS REGS
FROM users
GROUP BY EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)
) t1
LEFT JOIN
(
SELECT
  EXTRACT(YEAR FROM created_at) as YEAR,
  EXTRACT(MONTH FROM created_at) as MONTH,
  COUNT(*) AS REGS
FROM users
GROUP BY EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)
) t2 ON t2.YEAR = t1.YEAR
	AND t2.MOUNT = (t1.MOUNT) + 1 

ORDER BY t1.YEAR,t1.MONTH;

SELECT Train.nameTrain, Trip.time as date, 
Passanger.firstname, Passanger.lastname, Passanger.birthday, 
Price.wagon, Ticket.place, Price.price,
Ticket.departureStation, Ticket.arrivalStation,
dst.nameStation as dep, ast.nameStation as arr
FROM Ticket
JOIN Trip
  ON Ticket.idTrip = Trip.idTrip
JOIN Passanger
  ON Ticket.idPassanger = Passanger.idPassanger
JOIN Train
  ON Trip.idTrain = Train.idTrain
JOIN Price
  ON Train.idTrain = Price.idTrain AND Ticket.wagon = Price.wagon
JOIN Station dst
  ON Ticket.departureStation = dst.idStation 
JOIN Station ast
  ON Ticket.arrivalStation = ast.idStation