Prompt

For the same basis, how many distinct accounts had at least one AI Assist suggestion applied, split by partner practices versus client or direct businesses, and how many suggestions were generated (created) in total?

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 Activity Date",
  MAX(eligible_activity.last_activity_date) AS "Latest AI Assist Activity Date",
  COUNT(DISTINCT CASE
    WHEN eligible_activity.suggestions_applied > 0
      AND eligible_activity.finance_account_type = 'Accountant'
      THEN eligible_activity.account_crn
    WHEN eligible_activity.suggestions_applied > 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 Suggestions Applied",
  COUNT(DISTINCT CASE
    WHEN eligible_activity.suggestions_applied > 0
      AND eligible_activity.finance_account_type = 'Corporate'
      THEN eligible_activity.account_crn
  END) AS "Client or Direct Businesses With Suggestions Applied",
  COALESCE(SUM(eligible_activity.suggestions_created), 0) AS "AI Assist Suggestions Created"
FROM eligible_activity
LIMIT 1

Feedback

No feedback linked to this request.