yGj0YDTC

1CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
2
3DROP TABLE property cascade;
4DROP TABLE resident cascade;
5
6CREATE TABLE property AS SELECT 
7uuid_generate_v4 () AS property_id,
8md5(random()::text) AS address
9FROM generate_series(1,10);
10ALTER TABLE property ADD PRIMARY KEY (property_id);
11
12CREATE OR REPLACE FUNCTION choose_random_id()
13	RETURNS uuid
14	LANGUAGE plpgsql
15AS
16$$
17DECLARE
18	random_property_id uuid;
19BEGIN
20	SELECT property_id INTO random_property_id from property ORDER BY random() LIMIT 1;
21	RETURN random_property_id;
22END;
23$$;
24
25CREATE TABLE resident AS SELECT
26uuid_generate_v4 () AS resident_id,
27choose_random_id() AS property_id,
28md5(random()::text) AS description
29FROM generate_series(1,1000);
30ALTER TABLE resident ADD PRIMARY KEY(resident_id), ADD FOREIGN KEY(property_id) REFERENCES property(property_id);
31
32SELECT address
33FROM property AS p
34JOIN (
35	SELECT property_id
36	FROM resident
37	GROUP BY property_id
38	ORDER BY count(*) DESC
39	LIMIT 5
40) AS residents_count
41ON p.property_id = residents_count.property_id