syroco sql

ais-location---count-all-location-gaps.sql

-- COUNT ALL location gaps over a period
WITH AisLocationGaps AS (
    SELECT
        v.name,
        v."imoIdentifier",
        l.dtg,
        COALESCE(
            LAG(l.dtg) OVER (PARTITION BY v."activeMmsiIdentifier" ORDER BY l.dtg),
            NOW() - INTERVAL '4 months'
        ) AS previous_dtg,
        EXTRACT(EPOCH FROM l.dtg - COALESCE(
            LAG(l.dtg) OVER (PARTITION BY v."activeMmsiIdentifier" ORDER BY l.dtg),
            NOW() - INTERVAL '4 months'
        )) AS time_diff_in_seconds
    FROM
        aislocation l
    JOIN
        physicalvessel v ON v."activeMmsiIdentifier" = l.mmsi
    WHERE
        l.dtg >= NOW() - INTERVAL '4 months'  -- Limite aux 4 derniers mois
)
SELECT
    COUNT(dtg) AS gap_count,
    CASE 
        WHEN MAX(time_diff_in_seconds) >= EXTRACT(EPOCH FROM INTERVAL '1 day') THEN 
            FLOOR(MAX(time_diff_in_seconds) / EXTRACT(EPOCH FROM INTERVAL '1 day')) || ' jours'
        WHEN MAX(time_diff_in_seconds) >= EXTRACT(EPOCH FROM INTERVAL '1 hour') THEN 
            FLOOR(MAX(time_diff_in_seconds) / EXTRACT(EPOCH FROM INTERVAL '1 hour')) || ' heures'
        WHEN MAX(time_diff_in_seconds) >= EXTRACT(EPOCH FROM INTERVAL '1 minute') THEN 
            FLOOR(MAX(time_diff_in_seconds) / EXTRACT(EPOCH FROM INTERVAL '1 minute')) || ' minutes'
        ELSE 
            '< 1 minute'
    END AS max_duration
FROM
    AisLocationGaps
WHERE
    previous_dtg IS NOT NULL
    AND time_diff_in_seconds > EXTRACT(EPOCH FROM INTERVAL '15 minutes')

ais-location---count-location-gaps-per-vessel.sql

-- COUNT location gaps per vessel
WITH AisLocationGaps AS (
    SELECT
        v.name,
        v."imoIdentifier",
        l.dtg,
        COALESCE(
            LAG(l.dtg) OVER (PARTITION BY v."activeMmsiIdentifier" ORDER BY l.dtg),
            NOW() - INTERVAL '4 months'
        ) AS previous_dtg,
        EXTRACT(EPOCH FROM l.dtg - COALESCE(
            LAG(l.dtg) OVER (PARTITION BY v."activeMmsiIdentifier" ORDER BY l.dtg),
            NOW() - INTERVAL '4 months'
        )) AS time_diff_in_seconds
    FROM
        aislocation l
    JOIN
        physicalvessel v ON v."activeMmsiIdentifier" = l.mmsi
    WHERE
        l.dtg >= NOW() - INTERVAL '4 months'  -- Limite aux 4 derniers mois
)
SELECT
    name,
    "imoIdentifier",
    COUNT(dtg) AS gap_count,
    CASE 
        WHEN MAX(time_diff_in_seconds) >= EXTRACT(EPOCH FROM INTERVAL '1 day') THEN 
            FLOOR(MAX(time_diff_in_seconds) / EXTRACT(EPOCH FROM INTERVAL '1 day')) || ' jours'
        WHEN MAX(time_diff_in_seconds) >= EXTRACT(EPOCH FROM INTERVAL '1 hour') THEN 
            FLOOR(MAX(time_diff_in_seconds) / EXTRACT(EPOCH FROM INTERVAL '1 hour')) || ' heures'
        WHEN MAX(time_diff_in_seconds) >= EXTRACT(EPOCH FROM INTERVAL '1 minute') THEN 
            FLOOR(MAX(time_diff_in_seconds) / EXTRACT(EPOCH FROM INTERVAL '1 minute')) || ' minutes'
        ELSE 
            '< 1 minute'
    END AS max_duration
FROM
    AisLocationGaps
WHERE
    previous_dtg IS NOT NULL
    AND time_diff_in_seconds > EXTRACT(EPOCH FROM INTERVAL '15 minutes')
GROUP BY
    name, "imoIdentifier"
ORDER BY
    gap_count DESC;

ais-location---count-vessel-locations-between-dates.sql

-- count positions of a vessel between two dates
select count(l.dtg)
from physicalvessel v
join aislocation l on v."activeMmsiIdentifier" = l.mmsi
where v."imoIdentifier" = '9892494'
and l.dtg > '2025-01-01'
and l.dtg <'2025-02-18';

ais-location---count-vessel-locations.sql

-- count all vessel locations
select count(*)
from aislocation l
and l.dtg >'2025-02-18';

-- count all vessel locations for a specific vessel
select count(l.dtg)
from physicalvessel v
join aislocation l on v."activeMmsiIdentifier" = l.mmsi
where v."imoIdentifier" = '8912388'
and l.dtg > '2025-01-01'
and l.dtg <'2025-02-18';

ais-location---get-most-recent-plocation-of-each-vessel.sql

-- This query selects the most recent position date for each vessel and sorts the vessels by ascending date
SELECT 
	v.name,
    v."imoIdentifier",  -- Unique identifier for the vessel
    MAX(l.dtg) AS recent_position_date  -- Get the most recent position date for each vessel
FROM 
		physicalvessel v
JOIN 
    aislocation l ON v."activeMmsiIdentifier" = l.mmsi
GROUP BY 
    v."imoIdentifier"  -- Group by vessel identifier to get the latest position for each
ORDER BY 
    recent_position_date ASC;  -- Sort the results by the most recent position date in ascending order

ais-location---list-location-gaps-per-vessel.sql

-- This query list the location gaps for a given vessel
-- it compute the time differences between consecutive position reports
-- for a specific vessel and period and then checks for gaps until the current time.
WITH PositionGaps AS (
    -- Calculate time differences between consecutive position reports for the specified vessel
    SELECT
        v."imoIdentifier",  -- Vessel's unique identifier
        l.dtg,              -- Datetime of the position report
        COALESCE(
            LAG(l.dtg) OVER (PARTITION BY v."activeMmsiIdentifier" ORDER BY l.dtg),
            NOW() - INTERVAL '4 months'
        ) AS previous_dtg,
        EXTRACT(EPOCH FROM l.dtg - COALESCE(
            LAG(l.dtg) OVER (PARTITION BY v."activeMmsiIdentifier" ORDER BY l.dtg),
            NOW() - INTERVAL '4 months'
        )) AS time_diff_in_seconds
    FROM
        aislocation l
    JOIN
        physicalvessel v ON v."activeMmsiIdentifier" = l.mmsi
    WHERE
        v."imoIdentifier" = '9892494'  -- Filter for a specific vessel by IMO identifier
    AND
		dtg >= NOW() - INTERVAL '4 month'  -- Limit to last 4 months
),
LastPosition AS (
    -- Get the most recent position report datetime for the vessel
    SELECT
        "imoIdentifier",
        MAX(dtg) AS last_dtg  -- Most recent datetime of position report
    FROM
        PositionGaps
    GROUP BY
        "imoIdentifier"
)
SELECT
    pg."imoIdentifier",     -- Unique identifier for the vessel
    pg.dtg,                -- Datetime of the current position report
    pg.previous_dtg,       -- Datetime of the previous position report   
    CASE 
        WHEN pg.time_diff_in_seconds >= EXTRACT(EPOCH FROM INTERVAL '1 day') THEN 
            FLOOR(pg.time_diff_in_seconds / EXTRACT(EPOCH FROM INTERVAL '1 day')) || ' days'
        WHEN pg.time_diff_in_seconds >= EXTRACT(EPOCH FROM INTERVAL '1 hour') THEN 
            FLOOR(pg.time_diff_in_seconds / EXTRACT(EPOCH FROM INTERVAL '1 hour')) || ' hours'
        WHEN pg.time_diff_in_seconds >= EXTRACT(EPOCH FROM INTERVAL '1 minute') THEN 
            FLOOR(pg.time_diff_in_seconds / EXTRACT(EPOCH FROM INTERVAL '1 minute')) || ' minutes'
        ELSE 
            '< 1 minute'
    END AS duration,
    CASE 
        WHEN lp.last_dtg IS NOT NULL THEN 
            EXTRACT(EPOCH FROM NOW() - lp.last_dtg)  -- Calculate time since last position report in seconds
        ELSE 
            NULL  -- No last position available
    END AS seconds_since_last_position  -- Seconds since the last recorded position report
FROM
    PositionGaps pg
JOIN
    LastPosition lp ON pg."imoIdentifier" = lp."imoIdentifier"
WHERE
    previous_dtg IS NOT NULL
    AND pg.time_diff_in_seconds > EXTRACT(EPOCH FROM INTERVAL '15 minutes') -- Filter out consecutive positions with a time difference less than 15 minutes
ORDER BY
    pg.time_diff_in_seconds desc;  -- Order results by the datetime of position reports

ais-location---list-vessel-locations.sql

-- all vessel locations between two dates
select l.dtg as "date", l.sog as "speedOverGround", l.latitude, l.longitude
from physicalvessel v
join aislocation l on v."activeMmsiIdentifier" = l.mmsi
where v."imoIdentifier" = '9892494'
and l.dtg > '2025-01-01'
and l.dtg <'2025-02-18'
order by l."dtg" desc
limit 1;

custom-estimated-route---without-optimization.sql

select c.id, o.id from customestimatedroute c join optimization o on o."customEstimatedRouteId" = c."id" where o."id" is Null;

design---list-model-parameters.sql

select m.name, p.name, sp."defaultValue"
from public.scalarinputparameter sp
join public.inputparameter p on p.id = sp."inputParameterId"
join public.inputparametersonmodels j on j."inputParameterId" = sp."inputParameterId"
join public.model m on m.id = j."modelId"
where m."esCoreKey" = 'Rudder'
	and p."esCoreKey" = 'taper_ratio';

design---list-twin-project.sql

-- List twin projects
select m."twinProjectId", t.name, m.name, p."esCoreKey", s."defaultValue" from public.scalarinputparameter s
join public.inputparameter p on p.id = s."inputParameterId"
join public.inputparametersonmodels a on a."inputParameterId" = p.id
join public.model m on a."modelId" = m.id
join public.twinproject t on m."twinProjectId" = t.id
where a."modelId" in (select id from public.model m where m."esCoreKey" = 'Daggerboard')
and p."esCoreKey" = 'reference_surface';

design---update-parameter.sql

update public.scalarinputparameter
set "defaultValue" = 12
where "inputParameterId" in (
	select p.id from public.inputparameter p
  join public.inputparametersonmodels a on a."inputParameterId" = p.id
  where a."modelId" in (
    select id from public.model m where m."esCoreKey" = 'Daggerboard'
  )
  and p."esCoreKey" = 'reference_surface'
);

navigation-report---count-navigation-report.sql

-- Count NR with multiple draft
select
    count(distinct case when count_per_nr > 1 then g."navigationReportId" end) as nr_with_multiple_draft,  -- NR avec plusieurs occurrences
    min(case when count_per_nr > 1 then r."createdAt" end) as oldest_nr_with_multiple_draft,  -- La date la plus ancienne pour les NR multiples
    count(distinct g."navigationReportId") as nr_count,  -- Nombre total de NR
    min(r."createdAt") as oldest_nr  -- La date la plus ancienne parmi tous les NR
from navigationevent e
join generatedreportv2 g on g.id = e."generatedReportId"
join navigationreportv2 r on r.id = g."navigationReportId"
left join (
    select g."navigationReportId", count(*) as count_per_nr  -- Sous-requête pour compter les occurrences par NR
    from navigationevent e
    join generatedreportv2 g on g.id = e."generatedReportId"
    where e.type = 'DRAFT_CHANGE'
    group by g."navigationReportId"
) counts on counts."navigationReportId" = g."navigationReportId"
where e.type = 'DRAFT_CHANGE';

-- Draft change repartition
select 
    count_per_nr as draft_change_count,  -- Nombre de DRAFT_CHANGE par NR
    count(*) as nr_count                -- Nombre de NR ayant ce nombre de DRAFT_CHANGE
from (
    select 
        g."navigationReportId", 
        count(*) as count_per_nr        -- Compte des DRAFT_CHANGE par NR
    from navigationevent e
    join generatedreportv2 g on g.id = e."generatedReportId"
    where e.type = 'DRAFT_CHANGE'
    group by g."navigationReportId"
) subquery
group by count_per_nr
order by draft_change_count;


-- Last generations with status
select gr.id, gr."createdAt", pv.name, v.name, gr.status, gr."completionReason"
from generatedreportv2 gr
join navigationreportv2 nr on gr."navigationReportId" = nr.id
join vessel v on nr."vesselId" = v.id
join physicalvessel pv on v.imo = pv."imoIdentifier"
order by gr."createdAt" desc;

-- count generation per vessel
select g2."createdAt"::date, g2.status, p."name", count(*), array_agg(concat('https://bo.syro.co/navigation-reports/show/', n.id)) as "navigation_report_urls", array_agg(g2.id) as "generated_report_ids"
from generatedreportv2 g2  
join navigationreportv2 n on n.id = g2."navigationReportId"
join navigation n2 on n2.id = n."navigationId" 
join physicalvessel p on p."imoIdentifier" = n2."imoIdentifier"
where status != 'COMPLETED'
group by g2."createdAt"::date, g2.status, p."name"
order by 1 desc

navigation-report---generated-with-routing-objective.sql

-- list generated report with JITA segments
select distinct n.id as "NR id", 'https://bo.syro.co/navigation-reports/show/' || n.id AS "back office url"
from generatedreportv2 g
join navigationreportv2 n on n.id = g."navigationReportId"
join navigationevent e on e."generatedReportId" = g.id
where g."createdAt" >= '2025-04-03'
and (
  g."timeSpentInMinutesInJita" > 0
  or (
    g."timeSpentInMinutesWithoutSyroco" > 0
    and (
      g."timeSpentInMinutesInJita" > g."timeSpentInMinutesInBestTime"
      or (
        g."timeSpentInMinutesInJita" = 0
        and
        g."timeSpentInMinutesInBestTime" = 0
      )
    )
  )
);

navigation-report---get-detail.sql

-- Description: Get details of a specific generated report
select n.id as "nav", nr.id as "nr", g.id as "gen", n."departureDate", n."arrivalDate", g."createdAt", u.email as "by", g."debugFilePath" from generatedreportv2 g
join navigationreportv2 nr on g."navigationReportId" = nr.id
join navigation n on nr."navigationId" = n.id
join useraccount u on u.id = g."authorId"
where g.id in ('118381b5-6356-4a75-b2e6-bf78ff686d41')
order by n."arrivalDate" desc;

navigation-report---navigation-point.sql

select p."createdAt",
p."outgoingCurrentDirectionInDegrees",
p."currentSpeedInKnots",
p."incomingGroundWindDirectionInDegrees",
p."groundWindSpeedInKnots",
p."incomingTotalSeaDirectionInDegrees",
p."totalSeaHeightInMeters",
p."totalSeaPeriodInSeconds",
p."windWaveHeightInMeters",
p."windWavePeriodInSeconds",
p."incomingWindWaveDirectionInDegrees",
p."incomingTrueWindDirectionInDegrees",
p."trueWindSpeedInKnots",
p."swellHeightInMeters",
p."swellPeriodInSeconds",
p."incomingSwellDirectionInDegrees",
p."pressureInPascal",
p."temperatureInCelsius",
p."precipitationInMillimeters",
p."incomingCourseWindAngleInDegrees",
p."shipSpeedThroughWaterInKnots",
p."shipCourseThroughWaterInDegrees",
p."apparentWindSpeedInKnots",
p."incomingApparentWindAngleInDegrees",
p."incomingCurrentAngleInDegrees",
p."incomingTotalSeaAngleInDegrees"
from navigationpoint p
join segment s on s.id = p."segmentId"
join generatedreportv2 g on g.id = s."generatedReportId"
where g."navigationReportId" = '47c91771-11f7-4946-861d-b2dfb3cf6a58'
and p."incomingGroundWindDirectionInDegrees" is Null
and p."incomingTrueWindDirectionInDegrees" is not Null
order by p."createdAt"
desc limit 100;

optimisation---list-optimisations.sql

-- list last optimisations for a specific vessel
select s.name, v.id as "voyage", o."createdAt" as "optim creation", o."completedAt" as "optim completed at", o.status as "optim status", o."completionReason" as "optim reason",
v."createdAt" as "voyage creation", v."completedAt" as "voyage completed at", o.id
from optimization o
join voyage v on o."voyageId" = v.id
join vessel s on v."vesselId" = s.id
where s."imoIdentifier" = '8912388'
order by o."createdAt" DESC
limit 20;

-- list last optimization of a vessel
select o.id, o."createdAt", o."completedAt", o."completionReason", l."oldTheyrComputationState", l."newTheyrComputationState" from optimization o
join voyage v on o."voyageId" = v.id
join vessel ve on ve.id = v."vesselId"
join optimizationstatuslog l on o.id = l."optimizationId"
where ve.imo = '9789697' and l."newStatus" = 'COMPLETED'
order by o."createdAt" desc
limit 30;

-- list last optimization of a vessel
select p.name, ve.name, v.id as "vesselId", o.id as "omptimId", u.email, o."createdAt", o."completedAt" as "optim completed at", o."completionReason", l."oldTheyrComputationState", l."newTheyrComputationState"
from optimization o
join voyage v on o."voyageId" = v.id
join vessel ve on ve.id = v."vesselId"
join physicalvessel p on p."imoIdentifier" = ve.imo
join optimizationstatuslog l on o.id = l."optimizationId"
join useraccount u on o."authorId" = u.id
where p.name ilike '%fortune%'
order by o."createdAt" desc, l."createdAt" desc
limit 30;

-- list last failed optimizations of a vessel
WITH vars AS (
    SELECT NOW() AT TIME ZONE 'UTC' - INTERVAL '24 hours' AS cutoff_time
)
select distinct p.name, v.name, o.id, o."createdAt", o."voyageId" from vessel v
join physicalvessel p on p."imoIdentifier" = v."imo"
join voyage y on y."vesselId" = v.id
join optimization o on o."voyageId" = y.id
where o."completionReason" = 'FAILED'
and o."createdAt" >= vars.cutoff_time
and p.name ilike '%fortune%'
order by o."createdAt" desc
limit 30;

-- without custom estimated route
select count (*) from optimization o where  o."customEstimatedRouteId" is not null;

optimisation---optimization-time-debbuging.sql

-- list last optimization of a vessel
select o.id, o."createdAt", o."completedAt", o."completionReason", l."oldTheyrComputationState", l."newTheyrComputationState" from optimization o
join voyage v on o."voyageId" = v.id
join vessel ve on ve.id = v."vesselId"
join optimizationstatuslog l on o.id = l."optimizationId"
where ve.imo = '9789697' and l."newStatus" = 'COMPLETED'
order by o."createdAt" desc
limit 30;

-- count all optimization in a given period
select count(distinct l."optimizationId") from optimizationstatuslog l
where l."createdAt" > '2024-04-15 12:07:34.333'
and l."createdAt" < '2024-04-15 12:10:47.026';

-- voyage duration statistics
SELECT 
    AVG(EXTRACT(EPOCH FROM (dta - v."startTime")) / 86400) AS moyenne_duree,
    MIN(EXTRACT(EPOCH FROM (dta - v."startTime")) / 86400) AS min_duree,
    MAX(EXTRACT(EPOCH FROM (dta - v."startTime")) / 86400) AS max_duree,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (dta - v."startTime")) / 86400) AS mediane_duree
FROM voyage v
WHERE dta IS NOT NULL;

-- get the longest voyage
SELECT id, EXTRACT(EPOCH FROM (dta - v."startTime")) / 86400 AS duree
FROM voyage v
WHERE dta IS NOT NULL
ORDER BY duree DESC
LIMIT 1;

route-proposal---first-accepted-route-proposal.sql

select * from voyagerouteproposal p
where p."optimizationId" in (
  select "id" from optimization o
  where o."voyageId" = '060b6dd5-7f54-4cff-97c5-01e7de5a361e'
)
and p."completionReason" = 'ACCEPTED'
order by p."createdAt" ASC
limit 1;

route-proposal---list-accepted-route-proposals.sql

--  list last accepted voyage route proposals for a specific vessel
select p."createdAt", p."optimizationId", op."theyrVoyageId" ,  op."theyrComputationId",  op."voyageId", op.mode
from voyagerouteproposal p
join optimization op on op.id = p."optimizationId"
where p."optimizationId" in (
  select o."id" from optimization o
  join voyage v on v.id = o."voyageId"
  where v."vesselId" = '6618a599-4813-4b60-ad32-e77fb0b69e5c'
)
and p."completionReason" = 'ACCEPTED'
and p."createdAt" >= '2025-02-10'
and p."createdAt" <= '2025-02-23'
order by p."createdAt" ASC;

set-allowed-route-export-formats.sql

update vessel
set "allowedRouteExportFormats" = '{WARTSILA_NACOS_PLATINUM_XML,VOYAGE_OVERVIEW_WITH_WEATHER_CSV,SPERRY_MARINE_XML}'
where id = '7a7a009e-d98f-4a94-b6b3-86e0fe424068';

sync-completion-reasons-of-voyages.sql

update voyage v
set "completionReason" = (
  select l."newCompletionReason"
  from voyagestatuslog l
  where l."voyageId" = v.id
  order by l."createdAt" desc
  limit 1
)
where "completionReason" is Null;

vessel---find-vessel-with-polar.sql

select p.name as "physical vessel name", v.name as "vessel name", v.id as "vessel id", v."speedPolarTheyrVesselId", v."powerPolarTheyrVesselId", v."rpmPolarTheyrVesselId"
from physicalvessel p
join vessel v on v.imo = p."imoIdentifier"
where v."speedPolarTheyrVesselId" in ('08dcd695-7c87-44fe-825c-4fcc65b23c13')
or v."powerPolarTheyrVesselId" in ('08dcd695-7c87-44fe-825c-4fcc65b23c13')
or v."rpmPolarTheyrVesselId" in ('08dcd695-7c87-44fe-825c-4fcc65b23c13');

vessel---list-vessel.sql

-- List vessel with a given name
select p."imoIdentifier", p.name, v.name, p."activeMmsiIdentifier" as "mmsi", v."speedPolarTheyrVesselId", v."powerPolarTheyrVesselId", v."rpmPolarTheyrVesselId" from physicalvessel p
join vessel v on v.imo = p."imoIdentifier"
where p.name ilike '%freycinet%'
limit 10;

-- List vessel having a given polar
select p."imoIdentifier", p.name as "physical vessel name", v.name as "vessel name", v.id as "vessel id", v."speedPolarTheyrVesselId", v."powerPolarTheyrVesselId", v."rpmPolarTheyrVesselId"
from physicalvessel p
join vessel v on v.imo = p."imoIdentifier"
where v."speedPolarTheyrVesselId" in ('08dcd695-7c87-44fe-825c-4fcc65b23c13')
or v."powerPolarTheyrVesselId" in ('08dcd695-7c87-44fe-825c-4fcc65b23c13')
or v."rpmPolarTheyrVesselId" in ('08dcd695-7c87-44fe-825c-4fcc65b23c13');

-- List vessel without a polar
select p."imoIdentifier", p.name as "physical vessel name", v.name as "vessel name", v.id as "vessel id", v."speedPolarTheyrVesselId", v."powerPolarTheyrVesselId", v."rpmPolarTheyrVesselId"
from physicalvessel p
join vessel v on v.imo = p."imoIdentifier"
where v."speedPolarTheyrVesselId" is not null
and v."powerPolarTheyrVesselId" is not null
and v."rpmPolarTheyrVesselId" is not null;

vessel---update-export-format-to-vessel.sql

-- add
UPDATE vessel
SET "allowedRouteExportFormats" = array_append("allowedRouteExportFormats", 'CMA_GPX')
WHERE id = '389f0460-9df8-41fc-b887-1d241d7cedba';

-- remove
UPDATE vessel
SET "allowedRouteExportFormats" = array_remove("allowedRouteExportFormats", 'CMA_GPX')
WHERE id = '389f0460-9df8-41fc-b887-1d241d7cedba';

-- replace
update vessel
set "allowedRouteExportFormats" = '{WARTSILA_NACOS_PLATINUM_XML,VOYAGE_OVERVIEW_WITH_WEATHER_CSV,SPERRY_MARINE_XML}'
where id = '7a7a009e-d98f-4a94-b6b3-86e0fe424068';

voyage---list-voyage-in-progress-multiple-times.sql

select count(*) as "in progress count", l."voyageId"
FROM public.voyagestatuslog l
WHERE l."newStatus" = 'IN_PROGRESS'
group by l."voyageId"
order by count(*) DESC;