Cacher is the code snippet organizer for pro developers

SELECT date(`flights`.`created_at`) AS `date`,
sum(data->"$.main_adt") * quentity_adult  as main_adt,
sum(data->"$.main_chd") * quentity_child as main_chd,
sum(data->"$.main_inf") * quentity_infant as main_inf,
sum(data->"$.tax_adt")  * quentity_adult as tax_adt,
sum(data->"$.tax_chd")  * quentity_child as tax_chd,
sum(data->"$.tax_inf")  * quentity_infant as tax_inf,
sum(data->"$.soto_adt") * quentity_adult as soto_adt,
sum(data->"$.soto_chd") * quentity_child as soto_chd,
sum(data->"$.soto_inf") * quentity_infant as soto_inf
FROM `flights` 
INNER JOIN `passenger` ON `passenger`.`relation_id` = `flights`.`relation_id` 
INNER JOIN `user_invoice` ON `passenger`.`invoice_id` = `user_invoice`.`invoice_id` 
where passenger.reference_id is not null
and user_invoice.service_id = 4
and flights.org_id=27
GROUP BY date(`flights`.`created_at`)
order by `flights`.`created_at` desc;
-- latest group recor
select origin,destination, count(*) from request_detail
where id in (select distinct id from (select (select id from request_detail where request_id = a.request_id limit 1) as id from request_detail as a) as t)
group by origin,destination 

WITH ranked_messages AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;