Reporting Queries

Query timeout

For queries that take longer than the default timeout (2.5s), run the following command to extend the timeout.

Note: Please double check that you’re on a read replica before running potentially expensive queries.

ActiveRecord::Base.connection.execute 'SET statement_timeout = 200000'

Total Registered

Returns the number of users that created accounts (this includes users who may not have fully registered, see Fully Registered Users for that query)

User.count

To approximate the count at a past point in time, substitute date below:

date = Date.new(2021, 1, 1)
User.where('created_at <= ?', date).count

Fully Registered Users

Returns the number of fully registered users.

Note: This table has been backfilled as far back as 2017, so it is good for all time.

RegistrationLog.where.not(registered_at: nil).count

To approximate the count at a past point in time, substitute date below:

date = Date.new(2021, 1, 1)
RegistrationLog.where('registered_at <= ?', date).count

IAL2 Users

Returns the number of users with IAL2 credentials.

Profile.where(active: true).count

To approximate the count at a past point in time, substitute date below:

date = Date.new(2021, 1, 1)
Profile.where(active: true).where('activated_at < ?', date).count

Active Partners

Note: The queries below can be run locally in the 18F/identity-idp-config project by running bin/data_console from within the root directory, instead of from a production instance. You will need to remove the Agreements:: module from the Model name (i.e. start with just PartnerAccount) for queries run in the config repo.

The following query returns a list of the currently active Partner Accounts (i.e. organizations with an active IAA).

Agreements::PartnerAccount.includes(:agency, :partner_account_status).where(partner_account_statuses: { name: 'active' }).distinct

The following query will return a list of the partners with an active integration in production, which may be a subset of the list of active partners.

Agreements::PartnerAccount.includes(:agency, integrations: :service_provider).where(service_providers: { restrict_to_deploy_env: 'prod', active: true }).distinct