Блог

Я начинаю с SQL-запроса и нуждаюсь в некоторой помощи по приведенному ниже sql-запросу на основе схемы базы данных,

A) выведите общее количество открытых позиций (открытые позиции = вакансии — количество нанятых кандидатов) для каждой компании на текущую дату.

Я застрял после написания приведенного ниже набора кода

 SELECT coy.company_name, SUM(j.vacancies) as open_positions
FROM company AS coy
FULL JOIN jobs as j
  ON j.company_id = coy.company_id
FULL JOIN application as a
  ON a.job_id = j.job_id
FULL JOIN hired as h
  ON h.app_id = a.app_id
GROUP BY coy.company_name
ORDER BY coy.company_name
 

Схема базы данных приведена ниже.

 CREATE TABLE IF NOT EXISTS candidates (profile_id CHAR(11) PRIMARY KEY, first_name VARCHAR(32), last_name VARCHAR(32), country VARCHAR(16));
INSERT INTO candidates (profile_id, first_name, last_name, country) values (1, 'A_first', 'A_last', 'USA');
INSERT INTO candidates (profile_id, first_name, last_name, country) values (2, 'B_first', 'B_last', 'USA');
INSERT INTO candidates (profile_id, first_name, last_name, country) values (3, 'C_first', 'C_last', 'JPN');

CREATE TABLE IF NOT EXISTS company (company_id CHAR(11) PRIMARY KEY,company_name VARCHAR(64),industry VARCHAR(64));
INSERT INTO company (company_id, company_name, industry) values (1, 'A_coy', 'industry_A');
INSERT INTO company (company_id, company_name, industry) values (2, 'B_coy', 'industry_B');
INSERT INTO company (company_id, company_name, industry) values (3, 'C_coy', 'industry_C');

CREATE TABLE IF NOT EXISTS jobs (job_id CHAR(10) PRIMARY KEY, company_id CHAR(11) REFERENCES company (company_id), job_title VARCHAR(64), job_function VARCHAR(64), country VARCHAR(16), vacancies INTEGER NOT NULL);
INSERT INTO jobs (job_id, company_id, job_title, job_function, country, vacancies) values (1, 1, 'worker', 'technical', 'USA', 2);
INSERT INTO jobs (job_id, company_id, job_title, job_function, country, vacancies) values (2, 2, 'manager', 'admin', 'JPN', 1);
INSERT INTO jobs (job_id, company_id, job_title, job_function, country, vacancies) values (3, 3, 'supervisor', 'business', 'CAN', 1);

CREATE TABLE IF NOT EXISTS application (app_id CHAR(11) PRIMARY KEY, job_id CHAR(11) REFERENCES jobs (job_id), profile_id CHAR(11) REFERENCES candidates (profile_id), datetime TIMESTAMP);
INSERT INTO application (app_id, job_id, profile_id, datetime) values (1, 1, 1, '2021-1-1 10:00:00');
INSERT INTO application (app_id, job_id, profile_id, datetime) values (2, 2, 2, '2021-2-1 10:00:00');
INSERT INTO application (app_id, job_id, profile_id, datetime) values (3, 3, 3, '2021-3-1 10:00:00');

CREATE TABLE IF NOT EXISTS hired (app_id CHAR(11) REFERENCES application (app_id), datetime TIMESTAMP, hired BOOLEAN NOT NULL);
INSERT INTO hired (app_id, datetime, hired) values (1, '2021-1-1 10:00:00', TRUE);
INSERT INTO hired (app_id, datetime, hired) values (2, '2021-2-1 10:00:00', TRUE);
INSERT INTO hired (app_id, datetime, hired) values (3, '2021-3-1 10:00:00', FALSE);
 

Любая помощь будет очень признательна. Спасибо!