% of passage from stage to stage of the quiz funnel with questions in the same column

Link to the lesson

In step 2, you create the quiz funnel; then, Codecademy asks you to use an external spreadsheet program to calculate % of passage from stage to stage of the funnel. But is it possible to do in SQL if all questions and responses to the quiz are stored in the same table?

My try:

SELECT 
 question, 
 COUNT(user_id) AS '# of users',
 1.0 * COUNT(question = "1. What are you looking for?") / COUNT(response) * 100 AS '1st step passage %'
FROM survey
GROUP BY question
ORDER BY user_id;

Please help find out
Kind regards

I should not that SQL isn’t my best language so if anyone knows better by all means ignore this but I think it winds up being a mess in the version of SQLite available in the lesson; I don’t think you have access to window views or similar. I think the easiest thing to do is to create a new temporary table and perform the calculations there but even then I wound up with a mess and had to use INSERT and such.

If you’re up for a little bit of a challenge and some reading then you might find a way to do it but it’s debatable how worthwhile it might be. If you’re willing then it would be a bit of practice with some commands you don’t get to use much in the lessons prior to this project.

1 Like

I already tried a temporary table, no result.

WITH temp_table AS(
  SELECT *
  FROM survey
)
SELECT 
 survey.question, 
 COUNT(survey.user_id) AS '# of users',
 1.0 * COUNT(question = "1. What are you looking for?") / COUNT(response) * 100 AS '1st step passage %'
FROM survey
JOIN temp_table
  ON temp_table.user_id = survey.user_id
GROUP BY question
ORDER BY user_id;

I guess my question would be, why make it harder? I would just do the calcs in my head or with a calculator app…(which is what I did when I completed the project).

It’s not automatic, after any change in numbers you need to reperform calculation. So it can’t be used for real-time reports in BI tools like Amplitude or Tableau.

Yea, I get it.

Maybe try using the lag() function? Scroll down to the middle of this page:

1 Like

Thanks, you reminded me about LAG and I found correct query in this thread: Lead lag command

1 Like

Perfect, that’s what I wound up doing in such a clunky fashion. Took me ages, glad to hear there’s a proper way to do it, ta. Not sure if it works in the lesson or not (will give it a try) but glad to hear there is a correct way to go about it.

1 Like