You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
SELECTgroups.nameAS group,
users.nameAS user,
SUM(maps.mapviews) AS views
FROM
groups INNER JOIN groups_users ONgroups_users.group_id=groups.idINNER JOIN users ONusers.id=groups_users.user_idINNER JOIN maps ONusers.id=maps.user_idGROUP BYgroups.name, users.nameORDER BYgroups.nameASC, views DESC;
group | user | views
----------+---------+---------
Backend | Charles | 1223441
Engine | Bob | 1206145
Frontend | Daphne | 1282349
Leads | Alice | 1244137
Leads | Bob | 1206145
Madrid | Daphne | 1282349
Madrid | Alice | 1244137
Madrid | Charles | 1223441
Madrid | Bob | 1206145
Product | Alice | 1244137
Product | Charles | 1223441
(11 rows)
EJERCICIO 3: WINDOW FUNCTION
SELECTt.groupAS group,
t.userAS name,
t.viewsAS views,
round((t.views::decimal/ (SUM(t.views) OVER (PARTITION BY t.group)))*100,2) AS percent_of_group_views
FROM
(
SELECTgroups.nameAS group,
users.nameAS user,
SUM(maps.mapviews) AS views
FROM
groups INNER JOIN groups_users ONgroups_users.group_id=groups.idINNER JOIN users ONusers.id=groups_users.user_idINNER JOIN maps ONusers.id=maps.user_idGROUP BYgroups.name, users.nameORDER BYgroups.nameASC, views DESC
) AS t;
group | name | views | percent_of_group_views
----------+---------+---------+------------------------
Backend | Charles | 1223441 | 100.00
Engine | Bob | 1206145 | 100.00
Frontend | Daphne | 1282349 | 100.00
Leads | Alice | 1244137 | 50.78
Leads | Bob | 1206145 | 49.22
Madrid | Daphne | 1282349 | 25.87
Madrid | Alice | 1244137 | 25.10
Madrid | Charles | 1223441 | 24.69
Madrid | Bob | 1206145 | 24.34
Product | Alice | 1244137 | 50.42
Product | Charles | 1223441 | 49.58
(11 rows)