Skip to content

best-worst-questions.md

Latest commit

 

History

History
428 lines (358 loc) · 16.5 KB

best-worst-questions.md

File metadata and controls

428 lines (358 loc) · 16.5 KB

Goal: Get Best and Worst Question on average For Each Category

Examine our Data

Ok let's look at our table first. Each row has assessment results as a jsonb field. jsonb is a special Postgres data type that allows you to query within the table. Learn more about JSONB

assessment_result field looks like this:

{
    "Planning": {
        "Assigning heroic roles based on each hero's powers": 3,
        "Clearly defining how the villain would be defeated": 1,
        "Planning to minimize collateral damage to the city": 2,
        "Anticipating villain traps, twists, and dirty tricks": 1,
        "Reviewing the fight afterward to level up for next time": 2,
        "Preparing backup plans when the first plan inevitably failed": 2
    },
    "Teamwork": {
        "Communicating clearly during high-stakes fights": 1,
        "Training and supporting new heroes joining the team": 5,
        "Trusting teammates to handle their part of the fight": 2,
        "Handling ego clashes and dramatic tension constructively": 2,
        "Keeping morale high even when the villain had the upper hand": 5,
        "Executing multi-step plans without stepping on each other's capes": 1
    },
    "Gadgets": {
        "Keeping hero comms secure during the showdown": 5,
        "Defending against villain tech, hacks, or sabotage": 5,
        "Using sensors, scans, or intel to track villain activity": 1,
        "Using standard battle playbooks effectively in the field": 4,
        "Improvising heroically when gadgets broke or went missing": 1,
        "Building, upgrading, or repurposing gadgets under pressure": 1
    },
  ...
}

Our goal is to get average responses for each question, then choose the question that scores the best and the one that scores the worst for each category:

category best worst
Planning (best question result) (worst question result)
Gadgets (best question result) (worst question result)
Teamwork (best question result) (worst question result)

Step 1 - Get all the results from under the 'Planning' category

jsonb_each_text() converts the JSON object into multiple rows. You put the name of the column and the name of the field in the column, and it will give you the entries from just that field.

SELECT jsonb_each_text(assessment_result->'Planning')
FROM assessments

Example Results

Notice that the same questions will be repeated several times, because you are seeing each hero's answer for each question.

jsonb_each_text
("Assigning heroic roles based on each hero's powers",3)
("Clearly defining how the villain would be defeated",1)

Step 2 - make columns for the key (question name) and value (assessment result)

In this example: ("Assigning heroic roles based on each hero's powers",3), the question is the key, and the numeric answer is the value.

  • key -> "Assigning heroic roles based on each hero's powers"
  • value -> 3

CROSS JOIN LATERAL converts the results into columns for key and value. You can apply this command before the jsonb_each_text command.

SELECT key, value
FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Planning')

Example Results

Same questions are still repeated several times, but now key and value are split into two columns.

key value
Assigning heroic roles based on each hero's powers 3
Clearly defining how the villain would be defeated 1

Step 3 - now let's group those questions together and get average score for each question for a given category

  • AVG() - calculate an average on the key you've chosen
  • value::numeric - You have to cast the assessment value into a numeric value so it can actually average it.
  • GROUP BY key - You have to group together the questions so that the average is across results for the same question.
SELECT key AS question, AVG(value::numeric) AS avg_score
FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Planning')
GROUP BY key

Example Results

Same questions are still repeated several times, but now key and value are split into two columns.

question avg_score
Assigning heroic roles based on each hero's powers 2.677777
Clearly defining how the villain would be defeated 1.333333

Step 4 - recreate this query for each category we've got

Add this query for 'Gadgets' and 'Teamwork'

SELECT key AS question, AVG(value::numeric) AS avg_score
FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Gadgets')
GROUP BY key

SELECT key AS question, AVG(value::numeric) AS avg_score
FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Teamwork')
GROUP BY key

Ok now we have three separate queries. We don't want to have to hit the database 3 times for this data. What do you think we want to do next?

Step 5 - unite this data together

Use UNION ALL to join data from multiple queries. UNION ALL can be places between each query and since they all have the same fields, their results will be merged together.

SELECT key AS question, AVG(value::numeric) AS avg_score
FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Planning')
GROUP BY key
UNION ALL
SELECT key AS question, AVG(value::numeric) AS avg_score
FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Gadgets')
GROUP BY key
UNION ALL
SELECT key AS question, AVG(value::numeric) AS avg_score
FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Teamwork')
GROUP BY key

Example Results

question avg_score
Assigning heroic roles based on each hero's powers 2.677777
Clearly defining how the villain would be defeated 1.333333
Improvising heroically when gadgets broke or went missing 4.677777
Planning to minimize collateral damage to the city 2.333333

Nice! Now all the results are together. But I can't tell what category they belong to anymore. What should I do next?

Step 6 - Add another column for category.

We're doing this with a literal string so that we don't have to do an extra query to get that value. We could technically name it anything we want. Here's an example with just one category.

SELECT 'My Category' AS category, key AS question, AVG(value::numeric) AS avg_score
FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Planning')
GROUP BY key

And now the whole query:

SELECT 'Planning' as category, key AS question, AVG(value::numeric) AS avg_score
FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Planning')
GROUP BY key
UNION ALL
SELECT 'Gadgets' as category, key AS question, AVG(value::numeric) AS avg_score
FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Gadgets')
GROUP BY key
UNION ALL
SELECT 'Teamwork' as category, key AS question, AVG(value::numeric) AS avg_score
FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Teamwork')
GROUP BY key

Example Results

category question avg_score
Teamwork Assigning heroic roles based on each hero's powers 2.677777
Planning Clearly defining how the villain would be defeated 1.333333
Planning Planning to minimize collateral damage to the city 2.333333
Gadgets Improvising heroically when gadgets broke or went missing 4.677777

Ok now we want to query on our query to get the best question. This is where we need a CTE - Common Table Expression.

Step 7 - Save this query as a CTE

WITH question_avgs AS (query) allows you to save the results of your query as a temporary result set. You can then query on this set as if you were querying a table in your database. If you've heard of Views, it's almost like a temporary view you can embed in your query. In our case, we're saving it as question_avgs. Wrap your entire query with parentheses and name your CTE as question_avgs. Then select the three columns from your CTE.

WITH question_avgs AS (
  SELECT 'Planning' AS category, key AS question, AVG(value::numeric) AS avg_score
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Planning')
  GROUP BY key
  UNION ALL
  SELECT 'Gadgets', key, AVG(value::numeric)
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Gadgets')
  GROUP BY key
  UNION ALL
  SELECT 'Teamwork', key, AVG(value::numeric)
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Teamwork')
  GROUP BY key
)
SELECT category, question, avg_score
FROM question_avgs

Example Results

These results are unchanged! We are simply naming our old query and getting the same fields from it.

category question avg_score
Teamwork Assigning heroic roles based on each hero's powers 2.677777
Planning Clearly defining how the villain would be defeated 1.333333
Planning Planning to minimize collateral damage to the city 2.333333
Gadgets Improvising heroically when gadgets broke or went missing 4.677777

Ok now we need to sort these questions by their averages within each category and find the best one for that category. For that, we need to partition and label each row with its order.

Step 8 - Use Row numbers and Partitions to get the best question in each category

  • PARITION BY groups rows by category name.
  • ORDER BY sorts the rows by avg_score within their category name.
  • ROW_NUMBER assigns a sequential number to each row.

This combination means that each category will be sorted in descending order by avg_score and assigned a number, where 1 is the best score for each category.

WITH question_avgs AS (
  SELECT 'Planning' AS category, key AS question, AVG(value::numeric) AS avg_score
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Planning')
  GROUP BY key
  UNION ALL
  SELECT 'Gadgets', key, AVG(value::numeric)
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Gadgets')
  GROUP BY key
  UNION ALL
  SELECT 'Teamwork', key, AVG(value::numeric)
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Teamwork')
  GROUP BY key
)
    SELECT category, question, avg_score,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY avg_score DESC, question ASC) rn
    FROM question_avgs

Example Results

Now each category is sorted and rn is labeled with 1, 2, 3, 4 according its order.

category question avg_score rn
Teamwork Keeping morale high even when the villain had the upper hand 3.933777 1
Teamwork Assigning heroic roles based on each hero's powers 2.677777 2
Planning Planning to minimize collateral damage to the city 2.333333 1
Planning Clearly defining how the villain would be defeated 1.333333 2
Gadgets Improvising heroically when gadgets broke or went missing 4.677777 1
Gadgets Defending against villain tech, hacks, or sabotage 2.237777 2

Step 9 - Select the best question for each category

Which one is the best? Where row number (saved as rn) is equal to 1.

For this, we will wrap the select statement with a conditional SELECT statement that only takes rows where rn = 1. We are also renaming the question to best_question. Eg SELECT category, question AS best_question

WITH question_avgs AS (
  SELECT 'Planning' AS category, key AS question, AVG(value::numeric) AS avg_score
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Planning')
  GROUP BY key
  UNION ALL
  SELECT 'Gadgets', key, AVG(value::numeric)
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Gadgets')
  GROUP BY key
  UNION ALL
  SELECT 'Teamwork', key, AVG(value::numeric)
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Teamwork')
  GROUP BY key
)
  SELECT category, question AS best_question
  FROM (
    SELECT category, question, avg_score,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY avg_score DESC, question ASC) rn
    FROM question_avgs
  ) s WHERE rn = 1

Example Results

Now only the best questions are selected for each category.

category best_question
Teamwork Keeping morale high even when the villain had the upper hand
Planning Planning to minimize collateral damage to the city
Gadgets Improvising heroically when gadgets broke or went missing

Step 10 - Save best as a CTE

This way we can start to query for worst questions too. Don't forget your comma before best!

WITH question_avgs AS (
  SELECT 'Planning' AS category, key AS question, AVG(value::numeric) AS avg_score
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Planning')
  GROUP BY key
  UNION ALL
  SELECT 'Gadgets', key, AVG(value::numeric)
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Gadgets')
  GROUP BY key
  UNION ALL
  SELECT 'Teamwork', key, AVG(value::numeric)
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Teamwork')
  GROUP BY key
),
best AS (
  SELECT category, question AS best_question
  FROM (
    SELECT category, question,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY avg_score DESC, question ASC) rn
    FROM question_avgs
  ) s WHERE rn = 1
)
SELECT
  best.category,
  best.best_question
FROM best

Step 11 - Add worst CTE

Use best format to create a worst CTE. Only difference is the ORDER BY direction is ASC. And we'll name our selected question worst_question

WITH question_avgs AS (
  SELECT 'Planning' AS category, key AS question, AVG(value::numeric) AS avg_score
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Planning')
  GROUP BY key
  UNION ALL
  SELECT 'Gadgets', key, AVG(value::numeric)
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Gadgets')
  GROUP BY key
  UNION ALL
  SELECT 'Teamwork', key, AVG(value::numeric)
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Teamwork')
  GROUP BY key
),
best AS (
  SELECT category, question AS best_question
  FROM (
    SELECT category, question,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY avg_score DESC, question ASC) rn
    FROM question_avgs
  ) s WHERE rn = 1
),
worst AS (
  SELECT category, question AS worst_question
  FROM (
    SELECT category, question,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY avg_score ASC, question ASC) rn
    FROM question_avgs
  ) s WHERE rn = 1
)
SELECT
  worst.category,
  worst.worst_question
FROM worst

Step 12 - Join best and worst into same final SELECT

JOIN worst USING (category) matches the two CTE results where category is the same. This way we can have a row for each one.

WITH question_avgs AS (
  SELECT 'Planning' AS category, key AS question, AVG(value::numeric) AS avg_score
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Planning')
  GROUP BY key
  UNION ALL
  SELECT 'Gadgets', key, AVG(value::numeric)
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Gadgets')
  GROUP BY key
  UNION ALL
  SELECT 'Teamwork', key, AVG(value::numeric)
  FROM assessments CROSS JOIN LATERAL jsonb_each_text(assessment_result->'Teamwork')
  GROUP BY key
),
best AS (
  SELECT category, question AS best_question
  FROM (
    SELECT category, question,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY avg_score DESC, question ASC) rn
    FROM question_avgs
  ) s WHERE rn = 1
),
worst AS (
  SELECT category, question AS worst_question
  FROM (
    SELECT category, question,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY avg_score ASC, question ASC) rn
    FROM question_avgs
  ) s WHERE rn = 1
)
SELECT
  worst.category,
  worst.worst_question,
  best.best_question
FROM best
JOIN worst USING (category)

Step 13 - Use your query!

Add your query to frontend/app/page.tsx where the query placeholder is. Uncomment all these lines and paste your query into the "QUERY GOES HERE" part.

  // bestWorstQuestions = await query<{
  //   category: string;
  //   best_question: string;
  //   worst_question: string;
  // }>(`
  // ---
  // QUERY GOES HERE
  // ---
  // `);

Now go to your browser and refresh the app screen. You should see the best and worst questions under each appropriate heading.

🦸‍♀️ DONE! 🦸🏾‍♂️