search

medic / care-teams

For Product Management
0 stars 0 forks source link

PostgreSQL scripts to verify apdex test scenarios #63

Closed michaelkohn closed 3 months ago

michaelkohn commented 3 months ago

Write PostgreSQL queries that will verify all the Apdex tests highlighted in https://github.com/medic/care-teams/issues/5#issuecomment-1945908046 have successfully completed.

latin-panda commented 3 months ago

These are the telemetry data we expect from the automation test based on my understanding of this comment

The minimum number of currencies (count property) we expect to see for 1 user's 1 day of telemetry doc, after running the tests

From Slack, we plan to run these tests in 10 days.

latin-panda commented 3 months ago

This is block until the test are fixed and producing these data

latin-panda commented 3 months ago

We should do this for every config we are have selected.

michaelkohn commented 3 months ago

Let's keep this ticket (#63) for creating the base SQL statement for this and I'll go ahead and create individual tickets for customizing and running them on each of the different configs. I imagine you'll be able to borrow heavily from https://github.com/medic/care-teams/issues/3

Looks like we'll have Kenya and Nepal this week. Rafa said he'd run Togo, Mali, and Uganda on Monday next week.

I was thinking that the query would require the entry of a date range (the dates used to run the tests)

WHERE useview_telemetry_metrics.period_start BETWEEN <start_date> AND <end_date>

And the results would look something like this 👇🏼 , but if you have a better idea I'm all 👂🏼s.

  Expected Count (Minimum) Actual Count Pass?
Contact_list:load: 30 30 TRUE
Contact_list:query: 40 41 TRUE
contact_detail::load: 10 10 TRUE
contact_detail::load: 20 18 FALSE
contact_detail::load: 20 20 TRUE
enketo:contacts::<_action>:render: 10 10 TRUE
enketo:contacts::<_action>:save: 10 10 TRUE
enketo:reports:<_form>::render: 10 10 TRUE
enketo:reports:<_form>::save: 10 10 TRUE
Report_list:load: 10 10 TRUE
Report_list:query: 10 10 TRUE
report_detail:<_form>:load: 10 10 TRUE
Tasks:load: 20 20 TRUE
enketo:tasks:<_form>:<_action>:render: 20 20 TRUE
enketo:tasks:<_form>:<_action>:save: 10 10 TRUE
Tasks:refresh: 10 10 TRUE
Message_list:load: 10 10 TRUE
Messages_detail:load: 10 10 TRUE
Analytics:targets:load: 10 10 TRUE
Boot_time: 10 10 TRUE
latin-panda commented 3 months ago

Okay! That sounds good, so I'm going to unblock this one. Work on that SQL and submit a PR to include it in the cht-docs to preserve it for future runs.

latin-panda commented 3 months ago

This is the SQL; we need to change the forms' names according to the configs.

WITH 
  constants (days, start_date, end_date) AS (VALUES (10, '2024-02-12', '2024-02-17')),

  apdex_metrics AS(
    SELECT 
      substring(metric FROM '^(.*):apdex:') AS metric,
      SUM(COUNT) AS COUNT
    FROM useview_telemetry_metrics, constants
    WHERE 
      metric LIKE '%:apdex:%'
      AND period_start BETWEEN constants.start_date::DATE AND constants.end_date::DATE
    GROUP BY metric
  ),

  apdex_result as (
    SELECT 
      metric,
      CASE 
        WHEN apdex_metrics.metric = 'contact_list:load' THEN 3 * constants.days
        WHEN apdex_metrics.metric = 'contact_list:query' THEN 4 * constants.days
        WHEN apdex_metrics.metric = 'contact_detail:d_community_health_volunteer_area:load' THEN 1 * constants.days
        WHEN apdex_metrics.metric = 'contact_detail:e_household:load' THEN 2 * constants.days
        WHEN apdex_metrics.metric = 'contact_detail:f_client:load' THEN 2 * constants.days
        WHEN apdex_metrics.metric = 'enketo:contacts:form:contact:f_client:create:add:render' THEN 1 * constants.days
        WHEN apdex_metrics.metric = 'enketo:contacts:form:contact:f_client:create:add:save' THEN 1 * constants.days
        WHEN apdex_metrics.metric = 'enketo:reports:cha_signal_verification:add:render' THEN 1 * constants.days
        WHEN apdex_metrics.metric = 'enketo:reports:cha_signal_verification:add:save' THEN 1 * constants.days
        WHEN apdex_metrics.metric = 'report_list:load' THEN 1 * constants.days
        WHEN apdex_metrics.metric = 'report_list:query' THEN 1 * constants.days
        WHEN apdex_metrics.metric = 'report_detail:pregnancy_home_visit:load' THEN 1 * constants.days
        WHEN apdex_metrics.metric = 'tasks:load' THEN 2 * constants.days
        WHEN apdex_metrics.metric = 'tasks:refresh' THEN 1 * constants.days
        WHEN apdex_metrics.metric = 'enketo:tasks:postnatal_care_service:add:render' THEN 2 * constants.days
        WHEN apdex_metrics.metric = 'enketo:tasks:postnatal_care_service:add:save' THEN 1 * constants.days
        WHEN apdex_metrics.metric = 'message_list:load' THEN 1 * constants.days
        WHEN apdex_metrics.metric = 'messages_detail:load' THEN 1 * constants.days
        WHEN apdex_metrics.metric = 'analytics:targets:load' THEN 1 * constants.days
        WHEN apdex_metrics.metric = 'boot_time' THEN 1 * constants.days
      END AS expected_count,
      SUM(apdex_metrics.count) AS actual_count
    FROM apdex_metrics, constants
    GROUP BY apdex_metrics.metric, constants.days
  )

SELECT
  apdex_result.metric,
  apdex_result.expected_count,
  apdex_result.actual_count,
  CASE
    WHEN apdex_result.actual_count >= apdex_result.expected_count THEN 'TRUE' ELSE 'FALSE'
  END AS meet_expectation
FROM apdex_result
ORDER BY apdex_result.metric ASC

Output:

Screenshot 2024-03-28 at 4 46 22 PM
latin-panda commented 3 months ago

@michaelkohn I've added you as a reviewer of chore(#63): documents query to count apdex in a date range #1332

michaelkohn commented 3 months ago

Cool... and you can see above I've created the individual verification tickets. They are currently all "blocked", but I put them on next week's sprint for now since Rafa is planning to run all of them on Monday 🤞🏼