sql test task

queries.sql

-- https://onecompiler.com/mysql/44e3ra35j

-- create
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name varchar(255) NOT NULL,
  email varchar(255) NOT NULL,
  city_id INTEGER NOT NULL
);

CREATE TABLE cities (
  id INTEGER PRIMARY KEY,
  name varchar(255) NOT NULL
);

-- insert
INSERT INTO users VALUES (1, 'Clark', '[email protected]', 1);
INSERT INTO users VALUES (2, 'Dave', '[email protected]', 1);
INSERT INTO users VALUES (3, 'Ava', '[email protected]', 1);
INSERT INTO users VALUES (4, 'Martin', '[email protected]', 1);
INSERT INTO users VALUES (5, 'Liza', '[email protected]', 1);
INSERT INTO users VALUES (6, 'Bob', '[email protected]', 1);
INSERT INTO users VALUES (7, 'Greg', '[email protected]', 2);
INSERT INTO users VALUES (8, 'Linda', '[email protected]', 2);

INSERT INTO cities VALUES (1, 'Kyiv');
INSERT INTO cities VALUES (2, 'Zhytomyr');

-- fetch

-- SELECT email, COUNT(email) as count FROM users 
-- group by email
-- order by count desc

-- SELECT cities.name as city, COUNT(email) as count FROM users 
-- LEFT JOIN cities on cities.id = users.city_id
-- group by city
-- order by count desc

SELECT u1.name as name_1, u1.city_id as city_1, u2.name as name_2, u2.city_id as city_2 FROM users u1 
LEFT JOIN users u2 ON u2.email = u1.email
WHERE u1.city_id != u2.city_id AND u1.id < u2.id