Helpful SQL commands

Manipulate location data

Update location point

update "FlyingSites"
set point = ST_SetSRID(ST_MakePoint(9.7243166, 49.9896976), 4326)
where "shortName" = 'MAEUSBERG'

Count T-Shirts

SELECT COUNT(*) AS "numberOfPilots"
FROM (
  SELECT "userId"
  FROM "Flights"
  WHERE "flightPoints" > 60
    AND "takeoffTime" >= '2025-01-01'
    AND "takeoffTime" <  '2026-01-01'
  GROUP BY "userId"
  HAVING COUNT(*) >= 2
) AS pilots;

Find flights

By glider

SELECT *
FROM "Flights"
WHERE glider->>'model' = 'Mint'
AND EXTRACT(YEAR FROM "createdAt") = 2025;

In date range (without year)

select * from "Flights" 
WHERE (TO_CHAR("takeoffTime", 'MMDD') BETWEEN '0421' AND '0431')
and "flightDistance" > 15 
and "takeoffTime" > '01.01.2010'
and "siteId" in ('aa9b4b1e-24e1-4c31-ba2e-aee945a88941','4c81a555-fcba-4c79-8127-ece934e5b9b1','a4d07fb6-df15-420c-8e89-6265c8a9c2aa','28b733fa-a0a8-4c04-9ad8-cacec864f2e6','5727014e-2909-408b-833c-18125302889e','a6606392-22de-4395-86fb-5162c8f37a9b','790161fe-40b0-4ee8-bebe-a9cebc12d736')

Update flights

Glider class in flight

UPDATE "Flights"
-- SET glider = jsonb_set(glider, '{gliderClass,key}', '"C_high"')
-- SET glider = jsonb_set(glider, '{gliderClass,shortDescription}', '"GS Intermediate high"')
SET glider = jsonb_set(glider, '{gliderClass,description}', '"Schirme EN C mit einer Streckung >=6,4"')
WHERE glider->>'model' = 'Sage 105'
AND EXTRACT(YEAR FROM "createdAt") = 2025;

Find active user emails

WITH numbered AS (
  SELECT 
    "email",
    ROW_NUMBER() OVER (ORDER BY "updatedAt") AS rn
  FROM "Users"
  WHERE "updatedAt" > '2022-02-08'
  AND role != 'Inaktiv'
)
SELECT 
  string_agg(email, '; ') AS email_group
FROM numbered
GROUP BY (rn - 1) / 200
ORDER BY MIN(rn)