queries.sql

-- 1. Rank target roles by weighted skill match.
SELECT
    "role",
    "weighted_match_percent",
    "acquired_weight",
    "total_weight"
FROM "best_fit_roles";

-- 2. Find saved job postings for the current best-fitting role.
SELECT *
FROM "open_job_postings"
WHERE "role" = (
    SELECT "role"
    FROM "best_fit_roles"
    LIMIT 1
);

-- 3. Find high-priority skills still missing for Data Engineering roles.
SELECT
    "skill",
    "category",
    "priority"
FROM "missing_high_priority_skills"
WHERE "role" = 'Data Engineer'
ORDER BY "skill";

-- 4. Summarize the current application pipeline.
SELECT
    "status",
    "posting_count"
FROM "application_pipeline"
ORDER BY
    CASE "status"
        WHEN 'saved' THEN 1
        WHEN 'applied' THEN 2
        WHEN 'interview' THEN 3
        WHEN 'offer' THEN 4
        WHEN 'rejected' THEN 5
        ELSE 6
    END;

-- 5. Show upcoming saved postings with deadlines.
SELECT
    "company",
    "role",
    "title",
    "location",
    "application_deadline"
FROM "open_job_postings"
WHERE "application_deadline" IS NOT NULL
ORDER BY "application_deadline";

-- 6. Add a new posting using lookup subqueries.
INSERT INTO "job_postings"
    ("company_id", "role_id", "title", "location", "work_mode", "posting_url", "application_deadline", "status", "notes")
VALUES
    ((SELECT "id" FROM "companies" WHERE "name" = 'Celonis'),
     (SELECT "id" FROM "roles" WHERE "title" = 'Value Engineer'),
     'Value Engineer Graduate Program',
     'Munich',
     'hybrid',
     'https://example.com/celonis-graduate-value-engineer',
     '2026-09-15',
     'saved',
     'Follow up after strengthening dashboarding portfolio examples.');

-- 7. Update a posting as the application moves through the pipeline.
UPDATE "job_postings"
SET
    "status" = 'applied',
    "date_applied" = '2026-07-17',
    "notes" = 'Applied after tailoring the CV toward SQL, Python, and process mining.'
WHERE "company_id" = (SELECT "id" FROM "companies" WHERE "name" = 'Celonis')
  AND "role_id" = (SELECT "id" FROM "roles" WHERE "title" = 'Value Engineer')
  AND "title" = 'Value Engineer Working Student'
  AND "location" = 'Munich';

-- 8. Mark a skill as acquired and immediately improve role-fit scoring.
UPDATE "skills"
SET "acquired" = 1
WHERE "name" = 'Dashboarding';