create table universe
( id int primary key,
universe varchar(50),
budget double precision,
revenue double precision
);
create table characters
( id int primary key,
name varchar(50),
universe_id int references universe(id),
age int,
rating double precision,
last_seen timestamp
);
create table friends
( id int references characters(id),
friend_id int references characters(id),
friendship_years int
);
insert into universe values
(1,'Star Wars',135000,460000),
(2,'Star Trek',205250,566450)
;
insert into characters values
(1,'Luke Skywalker',1,20,4.5,'2021-04-05 6:30 PM'),
(2,'Han Solo',1,24,4.4,'2021-07-06 4:20 PM'),
(3,'Chewbacca',1,17,3.4,'2021-02-07 11:00 AM'),
(4,'Darth Vader',1,56,4.4,'2021-06-08 6:30 PM'),
(5,'Boba Fett',1,68,2.5,'2021-04-09 10:05 AM'),
(6,'R2D2',1,122,3.2,'2021-05-10 11:20 AM'),
(7,'Spock',2,35,4.6,'2021-04-11 9:05 AM'),
(8,'James T Kirk',2,32,4.4,'2021-03-12 5:20 PM'),
(9,'Scotty',2,31,2.2,'2021-01-13 9:38 PM'),
(10,'Nyota Uhura',2,28,4.0,'2021-02-14 8:30 PM')
;
insert into friends values
(1,6,20),
(2,3,10),
(8,7,2),
(8,9,4),
(7,10,2)
;
select characters.id,name,universe,budget, revenue,CAST(Round((revenue-budget)/universe_count) as integer)
from ( select universe_id, count(universe) as universe_count from universe
group by universe_id) as t1
join characters on t1.id=characters.universe_id
join universe on characters.universe_id=universe.id
Telegram: DB developers channel