Stuck Advanced Databases Exam Part 2

Language Help SQL
1

Hi,
I’m new at SQL language, I’m pursuing to be Data Analyst this year :slight_smile:
I’m following the “Data Science : Analytics” path on Codecademy. In the coding exam, I’m struggling with this question:

** You’ve been given a database for an online streaming service that first released in 2020. The database has a table customers with a column net_new that records net new monthly subscriptions to the streaming service broken down by tiers: free, standard, and premium.

Write a SELECT statement using windows functions to calculate two new columns from net_new:

  1. total: this column should contain a running total count of customers partitioned by the three subscription tiers
  2. year_over_year: for each month, this column should subtract the net_new value 12 months earlier from the current net_new value. This calculation should also be partitioned by level. Use the default LAG behaviour for the first 12 months (2020), which will be null.

Your final SELECT query should have the columns year, month, level, total, and year_over_yearin that order. Do not apply any additional order by statements, other than the ones needed within the window functions to properly perform the calculation.**

my code was:

<
SELECT year,
month,
level,
SUM(net_new) OVER(
PARTITION BY level
ORDER BY year, month
) AS ‘total’,
net_new - LAG(net_new, 12, 0) OVER(
PARTITION BY level
ORDER BY year, month
) AS ‘year_over_year’
FROM customers;
/>

I’ve tried tons of responses but all ended with this error message.
** Not quite! Check that your columns are in the right order, and that you’ve used SUM and LAG with the correct window syntax.**

Can somebody help me or explain to me ?

Thanks for you’re help !

1 Like
2

I’m sorry, but we cannot help out with any exams per community guidelines.

I’d recommend going back over how to use LAG & windows functions. Hopefully, you’ve already figured it out! :slight_smile:

3

Thank you for your response,
I see what you mean, but can you give at least an explanation why the code can’t be accepted as the correct answer?
In my opinion, I have answered the question. It’s just that the code I wrote isn’t exactly like the code codecademy wants :frowning:

So, I came here hoping I could get some insight to rewrite the code :slight_smile:

4

I haven’t (yet) taken this particular SQL exam.

So, you got the correct results, but didn’t get a point for answering the question, is that it?

To that, I’d say that there isn’t always just one way to arrive at an answer (b/c everyone’s brains work differently-- we learn differently & interpret things differently based on prior knowledge). If you got the same results and it (your code) doesn’t match w/what CC wanted, then I think that’s okay. Right? As long as you can explain your code and what it’s doing…in my opinion, that’s good. :woman_technologist:

1 Like
5

Could you did it? I’m stuck to, my code is similar of yours, but with some difference and it didn’t work. Do you have any tip? without, according to rules, give spoiling the answer. Thank you

6

Hi,
Yes, I have solved this problem.
Apparently the problem lies in a small detail that makes the code imperfect.

Hint: whether or not to use quotation marks (double and single) and the default LAG code (see LAG function default syntax).

I hope this could help :slight_smile:

9

hi I have the same problem but cannot understand where I am going wrong. the same error messsage occurs :
** Not quite! Check that your columns are in the right order, and that you’ve used SUM and LAG with the correct window syntax.**

10