Product analytics: Count number of users per service
What does this MR do?
We need to count for each service type the number of active users. This count the total number of users that have access to an active integration via project authorization, introducing new counters following this pattern: "projects_#{service_name}_active_users"
.
Event Dictionary updated.
Migration script output
rails db:migrate
$ rails db:migrate
== 20201123111156 ReplaceIndexOnProjectAuthorizationsForUsageData: migrating ==
-- transaction_open?()
-> 0.0000s
-- index_exists?(:project_authorizations, [:project_id, :user_id], {:name=>"index_project_authorizations_on_project_id_and_user_id", :algorithm=>:concurrently})
-> 0.0027s
-- transaction_open?()
-> 0.0000s
-- indexes(:project_authorizations)
-> 0.0016s
-- execute("SET statement_timeout TO 0")
-> 0.0003s
-- remove_index(:project_authorizations, {:algorithm=>:concurrently, :name=>"index_project_authorizations_on_project_id"})
-> 0.0045s
-- execute("RESET ALL")
-> 0.0002s
== 20201123111156 ReplaceIndexOnProjectAuthorizationsForUsageData: migrated (0.0103s)
rails db:migrate:down VERSION=20201123111156
$ rails db:migrate:down VERSION=20201123111156
== 20201123111156 ReplaceIndexOnProjectAuthorizationsForUsageData: reverting ==
-- transaction_open?()
-> 0.0000s
-- index_exists?(:project_authorizations, :project_id, {:name=>"index_project_authorizations_on_project_id", :algorithm=>:concurrently})
-> 0.0028s
-- execute("SET statement_timeout TO 0")
-> 0.0002s
-- add_index(:project_authorizations, :project_id, {:name=>"index_project_authorizations_on_project_id", :algorithm=>:concurrently})
-> 0.0037s
-- execute("RESET ALL")
-> 0.0002s
-- transaction_open?()
-> 0.0000s
-- indexes(:project_authorizations)
-> 0.0013s
-- remove_index(:project_authorizations, {:algorithm=>:concurrently, :name=>"index_project_authorizations_on_project_id_and_user_id"})
-> 0.0024s
== 20201123111156 ReplaceIndexOnProjectAuthorizationsForUsageData: reverted (0.0114s)
SQL Queries
SELECT MIN("project_authorizations"."user_id") FROM "project_authorizations"
Query plan: https://explain.depesz.com/s/R1Pg
SELECT MAX("project_authorizations"."user_id") FROM "project_authorizations"
Query plan: https://explain.depesz.com/s/ZO6H
SELECT COUNT(DISTINCT "project_authorizations"."user_id")
FROM "project_authorizations"
INNER JOIN projects ON projects.id = project_authorizations.project_id
INNER JOIN services ON services.project_id = projects.id
WHERE (services.active = TRUE)
AND (services.type = 'JiraService')
AND (projects.pending_delete = FALSE)
AND (projects.archived = FALSE)
AND "project_authorizations"."user_id" >= 905000
AND "project_authorizations"."user_id" < 910000
Query plan: https://explain.depesz.com/s/sf5g
Related to #225678 (closed)
Edited by Arturo Herrero