SELECT statement SQL

Data Science
1

Given this instruction:

"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 solution code:

SELECT YEAR("month") AS year, MONTH("month") AS month, "level" AS level, SUM(net_new) OVER (PARTITION BY "level" ORDER BY YEAR("month"), MONTH("month")) AS "total", net_new - LAG(net_new, 12, 0) OVER (PARTITION BY "level" ORDER BY YEAR("month"), MONTH("month")) AS "year_over_year" FROM customers;

I am stucked. Can someone help me out? What is wrong with my solution?

THANKS in advance!

2

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.