Most junior IT offers in Poland are not development jobs

-- Disclosed salaries for one city against fully remote offers.
--
-- Rows, not medians, for the same reason as salary_rows.sql: the caller attaches the
-- interval and the `n`.
--
-- Per ADVERT on the city arm, deliberately: a role advertised in eighteen cities really is
-- offered in eighteen cities, and collapsing it would erase the geography this comparison
-- exists to show. The remote arm is the opposite case and was wrong until an audit caught
-- it — all eighteen copies of one remote job land in the same bucket, which inflated that
-- arm by 28% (715 rows against 559 vacancies) and moved its median floor by 800 PLN. So the
-- two arms count differently, on purpose, and the reason is written here rather than left
-- for a reader to reverse-engineer.
--
-- The two groups overlap by construction — a Wrocław offer can also be remote — so this
-- compares two overlapping populations, not a partition. `group_name` labels which side a
-- row was counted on; a row can appear on both.
SELECT DISTINCT
    'city' AS group_name, sal.offer_id AS row_id, sal.monthly_from, sal.monthly_to
FROM offer_salaries sal
JOIN offer_locations loc ON loc.offer_id = sal.offer_id
JOIN sitemap_offers f ON f.offer_id = sal.offer_id
  AND f.last_seen = (SELECT MAX(last_seen) FROM sitemap_offers)
WHERE loc.city = $city
  AND sal.currency = $currency AND sal.kind = $kind AND sal.monthly_from IS NOT NULL

UNION ALL

SELECT DISTINCT
    'remote' AS group_name, COALESCE(o.vacancy_id, sal.offer_id) AS row_id,
    sal.monthly_from, sal.monthly_to
FROM offer_salaries sal
JOIN offer_work_modes wm ON wm.offer_id = sal.offer_id
JOIN sitemap_offers f ON f.offer_id = sal.offer_id
  AND f.last_seen = (SELECT MAX(last_seen) FROM sitemap_offers)
LEFT JOIN offers o ON o.offer_id = sal.offer_id
WHERE wm.work_mode = $work_mode
  AND sal.currency = $currency AND sal.kind = $kind AND sal.monthly_from IS NOT NULL

-- Bootstrapped by the caller, so the row order is part of the published figure.
ORDER BY group_name, row_id, monthly_from, monthly_to

-- How the analysed sample accumulated, run by run.
--
-- The claim this figure exists to check: a bounded, polite collector reaches a useful share
-- of the market by coming back, not by fetching harder. Every run appears — an `observe`
-- costs a handful of requests and moves the frame only, a `collect` also spends its bounded
-- page budget — so the flat steps are as much a part of the evidence as the rises.
--
-- The series is ordinal, not a time axis. Runs sit minutes or days apart, and spacing them
-- evenly along a date axis would suggest a rate nobody measured.
--
-- `offers_listed` is what the source published that day: the denominator of coverage, and
-- the ceiling the accumulation is climbing towards. A run recording one number but not the
-- other cannot be plotted and is left out rather than drawn against a missing base.
SELECT
    s.snapshot_id   AS snapshot_id,
    s.observed_date AS observed_date,
    s.kind          AS kind,
    MAX(CASE WHEN st.metric = 'coverage_fetched' THEN st.value END) AS offers_analysed,
    MAX(CASE WHEN st.metric = 'frame_live' THEN st.value END)       AS offers_listed
FROM snapshots s
JOIN snapshot_stats st ON st.snapshot_id = s.snapshot_id
GROUP BY s.snapshot_id, s.observed_date, s.kind
HAVING MAX(CASE WHEN st.metric = 'coverage_fetched' THEN st.value END) IS NOT NULL
   AND MAX(CASE WHEN st.metric = 'frame_live' THEN st.value END) IS NOT NULL
-- Run order, which is the only order that makes an accumulation monotonic.
ORDER BY s.snapshot_id

-- What kind of work the market is actually offering, optionally within one seniority.
--
-- LIVE OFFERS ONLY. The page calls itself a snapshot and the KPI reads "of the live
-- market", so the population is what the source lists on the observed date, not everything
-- ever collected. Without the join the analysed set becomes an archive that only grows:
-- two days in, 309 of 6839 analysed offers were already off the market, and at a few
-- hundred departures a day that share climbs indefinitely. Offers collected before the
-- frame existed have no row here and are excluded for the same reason — nothing records
-- whether they were still listed.
--
-- Counted per VACANCY, not per advert. One employer publishes a single role once per city —
-- 18 adverts for the same Cloud Data Engineer, same technologies, same salary — so counting
-- adverts lets posting volume stand in for demand. Measured when this changed: 4354 adverts
-- were 2856 vacancies, and the ranking moved azure from third place to seventh.
-- `vacancy_id` is a salted hash of title and company; an advert without one (either field
-- missing) falls back to its own id and stands alone, which is the safe direction.
--
-- This is the query behind the site's headline: filtered to juniors it shows that IT
-- support, not development, is the largest junior category — which is why an unfiltered
-- "top technologies for juniors" reports active directory and microsoft office.
--
-- "other" is included rather than hidden. Its share is published as a quality metric: a
-- rising "other" means data/normalization/role_families.yaml needs work, and dropping it
-- would quietly inflate every other family.
SELECT
    COALESCE(o.role_family, 'unclassified') AS role_family,
    COUNT(DISTINCT COALESCE(o.vacancy_id, o.offer_id)) AS offers
FROM offers o
LEFT JOIN offer_seniority s ON s.offer_id = o.offer_id
JOIN sitemap_offers f ON f.offer_id = o.offer_id
  AND f.last_seen = (SELECT MAX(last_seen) FROM sitemap_offers)
WHERE (CAST($seniority AS VARCHAR) IS NULL OR s.seniority = $seniority)
GROUP BY role_family
ORDER BY offers DESC, role_family

-- One row per live vacancy that discloses a monthly floor under `$kind`, with everything a
-- premium model is allowed to control for.
--
-- Rows, not an aggregate, for the reason `salary_rows` gives: the filter is the part that
-- must be defined exactly once, and the model that consumes these rows needs the
-- observations rather than a summary of them.
--
-- The floor, not the midpoint. A range's upper bound is disclosed less often and is the
-- more aspirational of the two numbers; modelling the floor keeps every row on the same
-- definition instead of silently mixing "floor" and "middle of a range".
--
-- Per VACANCY: one role published in eighteen cities is one salary offer, and the median of
-- its rows is taken because an employer can file several ranges against one job.
--
-- `kind` is B2B or employment and never pooled — they are different amounts of money for
-- the same work.
--
-- Seniority, work mode and technologies come back as pipe-joined lists rather than as
-- extra rows: a vacancy can carry several of each, and expanding them here would multiply
-- the salary across its own attributes. The model expands them into indicators.
--
-- City is deliberately absent. Under this project's unit of analysis a job is not in a
-- city — one vacancy is advertised across eighteen of them — so a city control would
-- describe the advert's distribution rather than the job's location.
WITH salaries AS (
    SELECT
        COALESCE(o.vacancy_id, s.offer_id) AS vacancy,
        MEDIAN(s.monthly_from)             AS monthly_floor
    FROM offer_salaries s
    JOIN offers o ON o.offer_id = s.offer_id
    JOIN sitemap_offers f ON f.offer_id = s.offer_id
      AND f.last_seen = (SELECT MAX(last_seen) FROM sitemap_offers)
    WHERE s.kind = $kind
      AND s.currency = $currency
      AND s.monthly_from IS NOT NULL
    GROUP BY vacancy
),
attributes AS (
    SELECT
        COALESCE(o.vacancy_id, o.offer_id)          AS vacancy,
        STRING_AGG(DISTINCT sn.seniority, '|')      AS seniority,
        STRING_AGG(DISTINCT w.work_mode, '|')       AS work_modes,
        STRING_AGG(DISTINCT CASE WHEN NOT $required_only OR t.required = 1
                                 THEN t.technology END, '|') AS technologies
    FROM offers o
    LEFT JOIN offer_seniority sn   ON sn.offer_id = o.offer_id
    LEFT JOIN offer_work_modes w   ON w.offer_id = o.offer_id
    LEFT JOIN offer_technologies t ON t.offer_id = o.offer_id
    GROUP BY vacancy
)
SELECT
    s.vacancy       AS vacancy,
    s.monthly_floor AS monthly_floor,
    a.seniority     AS seniority,
    a.work_modes    AS work_modes,
    a.technologies  AS technologies
FROM salaries s
JOIN attributes a ON a.vacancy = s.vacancy
ORDER BY s.vacancy

-- Individual disclosed salaries, filtered to one currency and one contract kind.
--
-- LIVE OFFERS ONLY. The page calls itself a snapshot and the KPI reads "of the live
-- market", so the population is what the source lists on the observed date, not everything
-- ever collected. Without the join the analysed set becomes an archive that only grows:
-- two days in, 309 of 6839 analysed offers were already off the market, and at a few
-- hundred departures a day that share climbs indefinitely. Offers collected before the
-- frame existed have no row here and are excluded for the same reason — nothing records
-- whether they were still listed.
--
-- Rows, not medians. The filter is the part that must be defined once; the aggregation is
-- a median either way, and returning rows lets the caller attach a confidence interval and
-- an honest `n` instead of publishing a bare number.
--
-- B2B (net on invoice) and employment (gross) are NEVER pooled: they are different
-- amounts of money for the same work, so the contract kind is a required parameter rather
-- than an optional filter. Currency is likewise fixed, because rates change and converting
-- would bake a date into the figure.
--
-- An offer listing several seniority levels contributes to each of them — an accepted
-- modelling choice for a market overview, not an oversight.
--
-- One row per VACANCY, not per advert. The same role published once per city repeats its
-- salary in every copy, so counting adverts would weight a median by how widely an employer
-- advertises rather than by what the market pays. DISTINCT over the vacancy and the figures
-- keeps a vacancy that genuinely states two different ranges as two rows.
SELECT DISTINCT
    COALESCE(o.vacancy_id, sal.offer_id) AS offer_id,
    s.seniority       AS seniority,
    o.role_family     AS role_family,
    sal.monthly_from  AS monthly_from,
    sal.monthly_to    AS monthly_to
FROM offer_salaries sal
JOIN offer_seniority s ON s.offer_id = sal.offer_id
LEFT JOIN offers o     ON o.offer_id = sal.offer_id
JOIN sitemap_offers f ON f.offer_id = sal.offer_id
  AND f.last_seen = (SELECT MAX(last_seen) FROM sitemap_offers)
WHERE sal.currency = $currency
  AND sal.kind = $kind
  AND sal.monthly_from IS NOT NULL
-- The caller bootstraps these rows, and a resampler draws in the order it is given, so the
-- published interval depends on this ordering. SQL promises none unless it is stated.
ORDER BY seniority, offer_id, monthly_from, monthly_to

-- How many vacancies stand behind each seniority level.
--
-- LIVE OFFERS ONLY. The page calls itself a snapshot and the KPI reads "of the live
-- market", so the population is what the source lists on the observed date, not everything
-- ever collected. Without the join the analysed set becomes an archive that only grows:
-- two days in, 309 of 6839 analysed offers were already off the market, and at a few
-- hundred departures a day that share climbs indefinitely. Offers collected before the
-- frame existed have no row here and are excluded for the same reason — nothing records
-- whether they were still listed.
--
-- Counted per VACANCY, not per advert. One employer publishes a single role once per city —
-- 18 adverts for the same Cloud Data Engineer, same technologies, same salary — so counting
-- adverts lets posting volume stand in for demand. Measured when this changed: 4354 adverts
-- were 2856 vacancies, and the ranking moved azure from third place to seventh.
-- `vacancy_id` is a salted hash of title and company; an advert without one (either field
-- missing) falls back to its own id and stands alone, which is the safe direction.
--
-- Published next to every seniority chart. Most strata in a bounded sample are small, and
-- a reader cannot judge a median without knowing whether it rests on 19 offers or 300.
SELECT
    s.seniority                AS seniority,
    COUNT(DISTINCT COALESCE(o.vacancy_id, s.offer_id)) AS offers
FROM offer_seniority s
LEFT JOIN offers o ON o.offer_id = s.offer_id
JOIN sitemap_offers f ON f.offer_id = s.offer_id
  AND f.last_seen = (SELECT MAX(last_seen) FROM sitemap_offers)
GROUP BY seniority
-- The tie-break is not cosmetic: this order reaches the page, and the guard in CI
-- compares the page byte for byte.
ORDER BY offers DESC, seniority

-- How each technology's share of the market moved, run over run.
--
-- The trap this query exists to avoid: a technology's recorded count rose on every early
-- run, and none of that was the market. Coverage went from 10% to 100% in four days, so
-- the counts were tracking our sample. Shares are the only comparable quantity here, and
-- even a share is only comparable between runs that observed a comparable slice: the fetch
-- queue takes every offer posted today plus a draw from the backlog, so a partial sample
-- leans towards recent postings and their technologies with it.
--
-- Hence `$min_coverage`. A run that observed less than that share of the live market is
-- left out of the comparison entirely, rather than plotted with a caveat under it. Runs
-- that recorded no coverage at all are out for the same reason: comparability has to be
-- established, not assumed.
--
-- One run per date — the last comparable one. Several runs share a day, and taking them
-- all would make a busy day look like a week of movement.
--
-- The series is read by name (`$series`) rather than assumed: metric names carry the
-- population they were measured over and change when it does, and a rename must show up
-- here as an empty result rather than as a silent splice of two different units.
WITH run_coverage AS (
    SELECT
        s.snapshot_id   AS snapshot_id,
        s.observed_date AS observed_date,
        MAX(CASE WHEN st.metric = 'coverage_fetched' THEN st.value END) AS analysed,
        MAX(CASE WHEN st.metric = 'frame_live' THEN st.value END)       AS listed
    FROM snapshots s
    JOIN snapshot_stats st ON st.snapshot_id = s.snapshot_id
    GROUP BY s.snapshot_id, s.observed_date
),
comparable AS (
    SELECT snapshot_id, observed_date, analysed / listed AS coverage
    FROM run_coverage
    WHERE analysed IS NOT NULL
      AND listed > 0
      AND analysed / listed >= $min_coverage
),
day_run AS (
    SELECT observed_date, MAX(snapshot_id) AS snapshot_id
    FROM comparable
    GROUP BY observed_date
)
SELECT
    m.dimension     AS technology,
    d.observed_date AS observed_date,
    m.value         AS vacancies,
    m.n             AS analysed_vacancies,
    m.value / m.n   AS share,
    c.coverage      AS coverage
FROM snapshot_dimension_metrics m
JOIN day_run d    ON d.snapshot_id = m.snapshot_id
JOIN comparable c ON c.snapshot_id = m.snapshot_id
WHERE m.metric = $series
  AND m.n > 0
ORDER BY m.dimension, d.observed_date

-- Which technologies are asked for together, beyond what their popularity explains.
--
-- The raw pair count answers a question nobody asked: the most frequent pair is always the
-- two most common technologies, so the ranking would just restate the demand chart twice.
-- What is worth publishing is the *lift* — how much more often two technologies share a
-- vacancy than they would if employers picked them independently. Lift 1 is chance; lift 6
-- means six times as often as chance, which is a stack, a certification track or a platform
-- rather than a coincidence.
--
-- Counted per VACANCY, like every other counting metric here: one role published in
-- eighteen cities is one job asking for that pair, not eighteen. The DISTINCT in `live`
-- collapses the per-city copies before anything is counted.
--
-- LIVE OFFERS ONLY, for the reason `top_technologies` states: the page is a snapshot of the
-- market, not of the archive.
--
-- `$min_pair_n` is the floor a pair has to clear before its lift is publishable. Lift is
-- unstable exactly where the data is thin — two technologies sharing four vacancies can
-- score higher than any real stack — so a rare pair is dropped rather than plotted as the
-- strongest finding on the page.
WITH live AS (
    SELECT DISTINCT
        COALESCE(o.vacancy_id, t.offer_id) AS vacancy,
        t.technology                       AS technology
    FROM offer_technologies t
    LEFT JOIN offers o ON o.offer_id = t.offer_id
    JOIN sitemap_offers f ON f.offer_id = t.offer_id
      AND f.last_seen = (SELECT MAX(last_seen) FROM sitemap_offers)
    WHERE (NOT $required_only OR t.required = 1)
),
totals AS (
    SELECT technology, COUNT(*) AS vacancies FROM live GROUP BY technology
),
analysed AS (
    SELECT COUNT(DISTINCT vacancy) AS vacancies FROM live
),
-- `b.technology > a.technology` does two jobs: it drops the self-pair and it keeps each
-- unordered pair once, so "python + sql" and "sql + python" cannot both be published.
pairs AS (
    SELECT a.technology AS technology_a, b.technology AS technology_b, COUNT(*) AS vacancies
    FROM live a
    JOIN live b ON b.vacancy = a.vacancy AND b.technology > a.technology
    GROUP BY a.technology, b.technology
)
SELECT
    p.technology_a AS technology_a,
    p.technology_b AS technology_b,
    p.vacancies    AS vacancies,
    ta.vacancies   AS vacancies_a,
    tb.vacancies   AS vacancies_b,
    n.vacancies    AS analysed_vacancies,
    (p.vacancies * 1.0 * n.vacancies) / (ta.vacancies * tb.vacancies) AS lift
FROM pairs p
JOIN totals ta ON ta.technology = p.technology_a
JOIN totals tb ON tb.technology = p.technology_b
CROSS JOIN analysed n
WHERE p.vacancies >= $min_pair_n
ORDER BY lift DESC, p.vacancies DESC, p.technology_a, p.technology_b
LIMIT $limit

-- Most in-demand technologies.
--
-- LIVE OFFERS ONLY. The page calls itself a snapshot and the KPI reads "of the live
-- market", so the population is what the source lists on the observed date, not everything
-- ever collected. Without the join the analysed set becomes an archive that only grows:
-- two days in, 309 of 6839 analysed offers were already off the market, and at a few
-- hundred departures a day that share climbs indefinitely. Offers collected before the
-- frame existed have no row here and are excluded for the same reason — nothing records
-- whether they were still listed.
--
-- Counts distinct vacancies rather than mentions, so neither one advert nor one
-- employer's per-city reposting counts twice.
--
-- Counted per VACANCY, not per advert. One employer publishes a single role once per city —
-- 18 adverts for the same Cloud Data Engineer, same technologies, same salary — so counting
-- adverts lets posting volume stand in for demand. Measured when this changed: 4354 adverts
-- were 2856 vacancies, and the ranking moved azure from third place to seventh.
-- `vacancy_id` is a salted hash of title and company; an advert without one (either field
-- missing) falls back to its own id and stands alone, which is the safe direction.
-- `required_only` (the default) excludes nice-to-haves: pooling them with must-haves
-- overweights optional skills and blurs the question "what does the market demand".
--
-- Optional filters: seniority, role family. Both matter more than they look — "top
-- technologies for juniors" without a role filter mixes developer roles with IT support
-- and reports a helpdesk profile.
SELECT
    t.technology                AS technology,
    COUNT(DISTINCT COALESCE(o.vacancy_id, t.offer_id)) AS offers
FROM offer_technologies t
LEFT JOIN offer_seniority s ON s.offer_id = t.offer_id
LEFT JOIN offers o          ON o.offer_id = t.offer_id
JOIN sitemap_offers f ON f.offer_id = t.offer_id
  AND f.last_seen = (SELECT MAX(last_seen) FROM sitemap_offers)
WHERE (NOT $required_only OR t.required = 1)
  AND (CAST($seniority AS VARCHAR) IS NULL OR s.seniority = $seniority)
  AND (CAST($role_family AS VARCHAR) IS NULL OR o.role_family = $role_family)
GROUP BY technology
ORDER BY offers DESC, technology
LIMIT $limit

-- How the market splits between remote, hybrid and office.
--
-- LIVE OFFERS ONLY. The page calls itself a snapshot and the KPI reads "of the live
-- market", so the population is what the source lists on the observed date, not everything
-- ever collected. Without the join the analysed set becomes an archive that only grows:
-- two days in, 309 of 6839 analysed offers were already off the market, and at a few
-- hundred departures a day that share climbs indefinitely. Offers collected before the
-- frame existed have no row here and are excluded for the same reason — nothing records
-- whether they were still listed.
--
-- Counted per VACANCY, not per advert. One employer publishes a single role once per city —
-- 18 adverts for the same Cloud Data Engineer, same technologies, same salary — so counting
-- adverts lets posting volume stand in for demand. Measured when this changed: 4354 adverts
-- were 2856 vacancies, and the ranking moved azure from third place to seventh.
-- `vacancy_id` is a salted hash of title and company; an advert without one (either field
-- missing) falls back to its own id and stands alone, which is the safe direction.
--
-- An offer can carry several work modes (hybrid and remote are often both listed), so the
-- shares deliberately sum to more than the offer count. Presenting this as a pie chart
-- would therefore be wrong; it is a bar chart of "offers mentioning this mode".
SELECT
    w.work_mode               AS work_mode,
    COUNT(DISTINCT COALESCE(o.vacancy_id, w.offer_id)) AS offers
FROM offer_work_modes w
LEFT JOIN offers o ON o.offer_id = w.offer_id
JOIN sitemap_offers f ON f.offer_id = w.offer_id
  AND f.last_seen = (SELECT MAX(last_seen) FROM sitemap_offers)
GROUP BY w.work_mode
ORDER BY offers DESC, w.work_mode