Carto BackEnd test

Carto_BackEnd_test.md

Ejercicio 1:

• 2 querys en total gracias al eager loading del preload
• En la app de RoR se crearían scopes en modelos y Query Objects para encapsular las responsabilidades para evitar potenciales smells

users = User
	.select('users.id, users.name, users.created_at, COUNT(groups_users.group_id) AS count_groups')
	.joins('users LEFT OUTER JOIN groups_users ON users.id=groups_users.user_id')
	.preload(:groups)
	.group('users.id')
	.order('count_groups ASC')

users.each do |user|
	puts user.name + ' - ' + user.crypted_name + ' - ' + user.count_groups + ' - ' + '[' + user.groups.map(&:name).join(' , ') + ']'
end

---

EJERCICIO 2: GROUP BY

	SELECT groups.name AS group, 
               users.name AS user, 
	       SUM(maps.mapviews) AS views
	FROM 
		groups INNER JOIN groups_users ON groups_users.group_id = groups.id
		INNER JOIN users ON users.id = groups_users.user_id
		INNER JOIN maps ON users.id = maps.user_id
	GROUP BY groups.name, users.name	
	ORDER BY groups.name ASC, 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

	SELECT t.group AS group, 
		 t.user AS name, 
		 t.views AS views, 
		 round((t.views::decimal/ (SUM(t.views) OVER (PARTITION BY t.group)))*100,2) AS percent_of_group_views
	FROM 
	(
	SELECT groups.name AS group, 
		   users.name AS user, 
		   SUM(maps.mapviews) AS views
	FROM 
	  groups INNER JOIN groups_users ON groups_users.group_id = groups.id
	  INNER JOIN users ON users.id = groups_users.user_id
	  INNER JOIN maps ON users.id = maps.user_id
	GROUP BY groups.name, users.name	
	ORDER BY groups.name ASC, 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)