Prompt

Total number of AI Assist suggestions created and applied to date (all time, global), and the number of partner practices and client businesses that have had at least one AI Assist suggestion.

SQL that ran

WITH activity_by_account AS (
  SELECT
    omni_dbt__eds_prepare_account_user_event_activity."ACCOUNT_CRN" AS account_crn,
    MIN(omni_dbt__eds_prepare_account_user_event_activity."OCCURRED_DATE") AS first_activity_date,
    MAX(omni_dbt__eds_prepare_account_user_event_activity."OCCURRED_DATE") AS last_activity_date,
    SUM(omni_dbt__eds_prepare_account_user_event_activity."SUGGESTION_CREATED_VOLUME") AS suggestions_created,
    SUM(omni_dbt__eds_prepare_account_user_event_activity."SUGGESTION_APPLIED_VOLUME") AS suggestions_applied
  FROM PROD."EDS_PREPARE_ACCOUNT_USER_EVENT_ACTIVITY" AS omni_dbt__eds_prepare_account_user_event_activity
  WHERE omni_dbt__eds_prepare_account_user_event_activity."OCCURRED_DATE" <= CURRENT_DATE()
    AND (
      omni_dbt__eds_prepare_account_user_event_activity."SUGGESTION_CREATED_VOLUME" > 0
      OR omni_dbt__eds_prepare_account_user_event_activity."SUGGESTION_APPLIED_VOLUME" > 0
    )
  GROUP BY omni_dbt__eds_prepare_account_user_event_activity."ACCOUNT_CRN"
),
current_accounts AS (
  SELECT DISTINCT
    omni_dbt__eds_all_accounts."ACCOUNT_CRN" AS account_crn,
    omni_dbt__eds_all_accounts."FINANCE_ACCOUNT_TYPE" AS finance_account_type,
    omni_dbt__eds_all_accounts."PARENT_ACCOUNT_CRN" AS parent_account_crn
  FROM PROD."EDS_ALL_ACCOUNTS" AS omni_dbt__eds_all_accounts
  INNER JOIN PROD."EDS_CALENDAR_DAY" AS omni_dbt__eds_calendar_day
    ON omni_dbt__eds_calendar_day."DATE_DAY" >= omni_dbt__eds_all_accounts."VALID_FROM" AND omni_dbt__eds_calendar_day."DATE_DAY" <= omni_dbt__eds_all_accounts."VALID_TO"
  WHERE omni_dbt__eds_calendar_day."IS_LATEST_AVAILABLE_DATE" = TRUE
    AND (omni_dbt__eds_all_accounts."IS_DEXT_DEMO" = FALSE OR omni_dbt__eds_all_accounts."IS_DEXT_DEMO" IS NULL)
    AND omni_dbt__eds_all_accounts."IS_SUSPENDED_DEXT" = FALSE
    AND omni_dbt__eds_all_accounts."SALESFORCE_ACCOUNT_TYPE" <> 'Reseller'
    AND omni_dbt__eds_all_accounts."ACCOUNT_CRN" IS NOT NULL
),
eligible_activity AS (
  SELECT
    current_accounts.account_crn,
    current_accounts.finance_account_type,
    current_accounts.parent_account_crn,
    activity_by_account.first_activity_date,
    activity_by_account.last_activity_date,
    activity_by_account.suggestions_created,
    activity_by_account.suggestions_applied
  FROM current_accounts
  INNER JOIN activity_by_account
    ON current_accounts.account_crn = activity_by_account.account_crn
)
SELECT
  MIN(eligible_activity.first_activity_date) AS "First AI Assist Suggestion Date",
  MAX(eligible_activity.last_activity_date) AS "Latest AI Assist Activity Date",
  COALESCE(SUM(eligible_activity.suggestions_created), 0) AS "AI Assist Suggestions Created",
  COALESCE(SUM(eligible_activity.suggestions_applied), 0) AS "AI Assist Suggestions Applied",
  COUNT(DISTINCT CASE
    WHEN eligible_activity.suggestions_created > 0
      AND eligible_activity.finance_account_type = 'Accountant'
      THEN eligible_activity.account_crn
    WHEN eligible_activity.suggestions_created > 0
      AND eligible_activity.finance_account_type = 'Corporate'
      AND eligible_activity.parent_account_crn IN (
        SELECT account_crn
        FROM current_accounts
        WHERE finance_account_type = 'Accountant'
      )
      THEN eligible_activity.parent_account_crn
  END) AS "Partner Practices With At Least One Suggestion",
  COUNT(DISTINCT CASE
    WHEN eligible_activity.suggestions_created > 0
      AND eligible_activity.finance_account_type = 'Corporate'
      THEN eligible_activity.account_crn
  END) AS "Client Businesses With At Least One Suggestion"
FROM eligible_activity
LIMIT 1

Feedback

No feedback linked to this request.