Query for Natasha's question posed on 14-04-2025

natasha-20250415-001.sql

SELECT 
  acc.email,
  a1.program_user_id,
  a1.assessment_id AS initial_assessment_id,
  a2.assessment_id AS retake_assessment_id,
  s1.statement_id AS statement_id,
FROM assessments a1
JOIN assessments a2 ON a2.program_user_id = a1.program_user_id
JOIN scores s1 ON s1.assessment_id = a1.assessment_id
JOIN scores s2 ON s2.assessment_id = a2.assessment_id
JOIN achievements ach ON ach.assessment_id = a1.assessment_id
JOIN challenges ch ON ch.challenge_id = ach.challenge_id
JOIN program_users pu ON a1.program_user_id = pu.program_user_id
JOIN accounts acc ON acc.account_id = pu.account_id
JOIN programs p ON p.program_id = pu.program_id
WHERE a1."order" = a2."order" - 1
  AND s1.statement_id = s2.statement_id
  AND s1."value" < s2."value" - 50
  AND ch.statement_id = s1.statement_id
  AND NOT p.not_for_analysis
GROUP BY acc.email, a1.program_user_id, initial_assessment_id, retake_assessment_id, s1.statement_id
HAVING COUNT(*) >= 4

This query is attempting to address the following analysis.

I am attempting to construct a query to identify the people who have had a 50+% increase in a statement score between two assessments and have done at least 4 challenges (to contribute to that change). For the statement where there is a 50% increase, obviously the first score needs to be less than 50%.