/*
citizens citizenships
+------+---------+ +-------------+-----+ cities
| id | int |<---| citizen_id | int | +-------+---------+
| name | varchar | | city_id | int |--->| id | int |
+------+---------+ +-------------+-----+ | name | varchar |
+-------+---------+
*/
drop table if exists citizens, cities, citizenships;
create table citizens (
id int not null,
name varchar(255),
primary key (id)
);
create table cities (
id int not null,
name varchar(255),
primary key (id)
);
create table citizenships (
citizen_id int not null,
city_id int not null,
primary key (citizen_id, city_id)
);
insert into citizens (id, name) values
(1, 'Anna'),
(2, 'Julia'),
(3, 'Anastasia');
insert into cities (id, name) values
(1, 'Los Angeles'),
(2, 'Moscow'),
(3, 'St. Petersburg'),
(4, 'New York');
insert into citizenships (citizen_id, city_id) values
(1, 1), (1, 2), (1, 3),
(2, 1), (2, 2), (2, 3), (2, 4),
(3, 1), (3, 2), (3, 4);
-- Find all citizens living in all requested cities at the same time
-- Example below:
/*
User requested: I need to see people who live in LA, MSK and SPb:
---------------------------------------------------------
Citizens | Cities
---------------------------------------------------------
Anna | Los Angeles, Moscow, St. Petersburg - RETURNED
Julia | Los Angeles, Moscow, St. Petersburg, New York - RETURNED
Anastasia | Los Angeles, Moscow, New York - NOT returned
---------------------------------------------------------
*/
-- TODO: Write your query here
select citizens.name as "Citizens", string_agg(cities.name, ', ') as "Cities" from citizens
left outer join citizenships on citizens.id = citizenships.citizen_id
left outer join cities on cities.id = citizenships.city_id
where citizens.id in (
select citizens_id from (
select citizens.id as "citizens_id", string_agg(CAST(cities.id AS varchar(10)), ',') as "cities_ids" from citizens
left outer join citizenships on citizens.id = citizenships.citizen_id
left outer join cities on cities.id = citizenships.city_id
where citizenships.city_id in (1,2,3)
group by citizens.id
) as citizens_ids
where cities_ids = '1,2,3'
) group by citizens.name, citizen_id;
Created
January 28, 2022 20:25
-
-
Save Mifrill/9adf378ce7b88c226bcafc2ece225f38 to your computer and use it in GitHub Desktop.
Find all citizens living in all requested cities at the same time
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment