CTE va rekursiv so'rovlar

CREATE TABLE xodim (
    id       int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ism      text NOT NULL,
    lavozim  text NOT NULL,
    rahbar_id int REFERENCES xodim(id),
    maosh    numeric(10,2) NOT NULL
);

INSERT INTO xodim (ism, lavozim, rahbar_id, maosh) VALUES
    ('Husanboy', 'direktor',        NULL, 18000000),
    ('Malika',   'bo''lim boshlig''i', 1,  12000000),
    ('Nodira',   'bo''lim boshlig''i', 1,  11500000),
    ('Aziza',    'mutaxassis',         2,   7000000),
    ('Kamola',   'mutaxassis',         2,   6800000),
    ('Dilnoza',  'mutaxassis',         3,   7200000),
    ('Bekzod',   'stajyor',            4,   3000000);

WITH bolim_ortacha AS (
    SELECT lavozim, avg(maosh) AS ortacha
    FROM xodim
    GROUP BY lavozim
)
SELECT x.ism, x.lavozim, x.maosh, round(b.ortacha, 0) AS lavozim_ortacha
FROM xodim x
JOIN bolim_ortacha b ON b.lavozim = x.lavozim
WHERE x.maosh > b.ortacha
ORDER BY x.maosh DESC;

WITH bosh AS (
    SELECT * FROM xodim WHERE rahbar_id IS NOT NULL
),
yuqori_maosh AS (
    SELECT * FROM bosh WHERE maosh > 7000000
)
SELECT ism, lavozim, maosh FROM yuqori_maosh ORDER BY maosh DESC;

WITH RECURSIVE daraxt AS (
    SELECT id, ism, lavozim, rahbar_id, 1 AS daraja
    FROM xodim
    WHERE rahbar_id IS NULL

    UNION ALL

    SELECT x.id, x.ism, x.lavozim, x.rahbar_id, d.daraja + 1
    FROM xodim x
    JOIN daraxt d ON x.rahbar_id = d.id
)
SELECT daraja, repeat('  ', daraja - 1) || ism AS ierarxiya, lavozim
FROM daraxt
ORDER BY daraja, ism;

WITH RECURSIVE yol AS (
    SELECT id, ism, rahbar_id, ism AS zanjir
    FROM xodim WHERE rahbar_id IS NULL

    UNION ALL

    SELECT x.id, x.ism, x.rahbar_id, y.zanjir || ' > ' || x.ism
    FROM xodim x JOIN yol y ON x.rahbar_id = y.id
)
SELECT ism, zanjir FROM yol ORDER BY zanjir;

WITH RECURSIVE xavfsiz AS (
    SELECT id, ism, rahbar_id, ARRAY[id] AS korilgan
    FROM xodim WHERE rahbar_id IS NULL

    UNION ALL

    SELECT x.id, x.ism, x.rahbar_id, s.korilgan || x.id
    FROM xodim x
    JOIN xavfsiz s ON x.rahbar_id = s.id
    WHERE NOT x.id = ANY(s.korilgan)
)
SELECT ism, korilgan FROM xavfsiz ORDER BY id;

WITH RECURSIVE sanoq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM sanoq WHERE n < 5
)
SELECT n, n * n AS kvadrat FROM sanoq;