clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799399 fiddles created (41688 in the last week).

CREATE TABLE Employees ( Employee VARCHAR(30) NOT NULL PRIMARY KEY ); CREATE TABLE Roles ( Role VARCHAR(30) NOT NULL PRIMARY KEY ); CREATE TABLE AppSystemCategories ( Category VARCHAR(30) NOT NULL PRIMARY KEY ); CREATE TABLE AppSystems ( AppSystem VARCHAR(30) NOT NULL PRIMARY KEY, Category VARCHAR(30) NOT NULL REFERENCES AppSystemCategories(Category) ); CREATE TABLE EmployeeAppSystems ( Employee VARCHAR(30) NOT NULL REFERENCES Employees(Employee), AppSystem VARCHAR(30) NOT NULL REFERENCES AppSystems(AppSystem), PRIMARY KEY (Employee, AppSystem) ); CREATE TABLE RoleAppSystems ( Role VARCHAR(30) NOT NULL REFERENCES Roles(Role), AppSystem VARCHAR(30) NOT NULL REFERENCES AppSystems(AppSystem), PRIMARY KEY (Role, AppSystem) ); INSERT INTO Employees(Employee) VALUES ('bibi'),('tee'),('mimi'),('lee'), ('min'); INSERT INTO Roles(Role) VALUES ('Data Analyst'), ('Engineer'), ('Business'); INSERT INTO AppSystemCategories(Category) VALUES ('Tech'), ('Biz'); INSERT INTO AppSystems(AppSystem, Category) VALUES ('Redshift','Tech'), ('Redash','Tech'), ('Jira','Tech'), ('Tableau','Tech'), ('Figma','Tech'), ('Office','Tech'), ('MoneyForward','Biz'), ('Salesforce','Biz'), ('Notion','Biz'); INSERT INTO RoleAppSystems(Role, AppSystem) VALUES ('Data Analyst','Redshift'), ('Data Analyst','Redash'), ('Data Analyst','Tableau'), ('Data Analyst','MoneyForward'), ('Data Analyst','Notion'), ('Engineer','Figma'), ('Engineer','Jira'), ('Engineer','MoneyForward'), ('Engineer','Notion'), ('Business','Notion'), ('Business','MoneyForward'), ('Business','Office'); -- MoneyForward and Notion are used by all roles INSERT INTO EmployeeAppSystems(Employee, AppSystem) VALUES ('bibi','Redshift'), ('bibi','Redash'), ('bibi','MoneyForward'), ('bibi','Notion'), -- Partial match for Data Analyst, match for Biz systems ('tee','Redshift'), ('tee','Redash'), ('tee','Tableau'), ('tee','MoneyForward'), ('tee','Notion'), -- Perfect match for Data Analyst ('mimi','Figma'), ('mimi','Jira'), ('mimi','MoneyForward'), ('mimi','Notion'), ('mimi','Tableau'), -- Over qualified for Engineer (uses extra systems) ('lee','Notion'), ('lee','Salesforce'), -- No tech skills ('min','Redshift'), ('min','Redash'), ('min','Jira'), ('min','Tableau'), ('min','Figma'), ('min','Office'), ('min','MoneyForward'), ('min','Salesforce'), ('min','Notion'); -- min Has it all -- all candidates use notion
5 rows affected
3 rows affected
2 rows affected
9 rows affected
12 rows affected
25 rows affected
 hidden batch(es)


SELECT * FROM AppSystems; SELECT * FROM AppSystemCategories; SELECT * FROM Employees; SELECT * FROM EmployeeAppSystems; SELECT * FROM Roles; SELECT * FROM RoleAppSystems;
appsystem category
Redshift Tech
Redash Tech
Jira Tech
Tableau Tech
Figma Tech
Office Tech
MoneyForward Biz
Salesforce Biz
Notion Biz
category
Tech
Biz
employee
bibi
tee
mimi
lee
min
employee appsystem
bibi Redshift
bibi Redash
bibi MoneyForward
bibi Notion
tee Redshift
tee Redash
tee Tableau
tee MoneyForward
tee Notion
mimi Figma
mimi Jira
mimi MoneyForward
mimi Notion
mimi Tableau
lee Notion
lee Salesforce
min Redshift
min Redash
min Jira
min Tableau
min Figma
min Office
min MoneyForward
min Salesforce
min Notion
role
Data Analyst
Engineer
Business
role appsystem
Data Analyst Redshift
Data Analyst Redash
Data Analyst Tableau
Data Analyst MoneyForward
Data Analyst Notion
Engineer Figma
Engineer Jira
Engineer MoneyForward
Engineer Notion
Business Notion
Business MoneyForward
Business Office
 hidden batch(es)


-- 集計関数を使う方法(Aggregation) -- データアナリスト用システムのみを利用する社員 with /* データアナリスト用システムを取得します。*/ data_analyst_systems as ( select rs.AppSystem from RoleAppSystems as rs where rs.Role = 'Data Analyst' ) /* データアナリスト用システムのみを利用する社員を取得します。*/ select eas.Employee from EmployeeAppSystems as eas left outer join data_analyst_systems as das on das.AppSystem = eas.AppSystem group by eas.Employee having count(das.AppSystem) = (select count(*) from data_analyst_systems) and count(das.AppSystem) = count(*);
employee
tee
 hidden batch(es)


-- 複雑になってしまっている例 -- データアナリスト用システムのみを利用する社員 with /* データアナリスト用システムを取得して、 全ての社員と cross join して直積結合を作ります。*/ data_analyst_systems as ( select * from (select distinct(AppSystem) as DataAnalystAppSystems from RoleAppSystems where Role = 'Data Analyst' ) as app_systems cross join Employees ), /* データアナリスト用システムを使わない社員を取得します。*/ employee_not_using_da_systems as ( select das.Employee from data_analyst_systems as das left join EmployeeAppSystems as eas on eas.AppSystem = das.DataAnalystAppSystems and eas.Employee = das.Employee where eas.Employee is null group by das.Employee ), /* 全ての社員からデータアナリスト用システムを使わない社員を除外し、 データアナリスト用システムを利用する社員を取得します。*/ employee_uses_da_systems as ( select e.Employee from Employees as e left join employee_not_using_da_systems as n on e.Employee = n.Employee where n.Employee is null ), /* データアナリスト用システムもほかのロール用システムも利用する社員を取得します。 */ employee_uses_notonly_da_systems as ( select da.Employee from employee_uses_da_systems as da left join EmployeeAppSystems as eas on da.Employee = eas.Employee left join (select * from RoleAppSystems where Role = 'Data Analyst') as ras on eas.AppSystem = ras.AppSystem where ras.Role is null group by da.Employee ), /* データアナリスト用システムを利用する社員から他のロール用システムも利用する社員を除外します。*/ employee_uses_only_da_systems as ( select e.Employee from employee_uses_da_systems as e left join employee_uses_notonly_da_systems as n on e.Employee = n.Employee where n.Employee is null ) select * from employee_uses_only_da_systems;
employee
tee
 hidden batch(es)