0

I have a table that counts logins to a website with the first column, USER_ID, being the user's unique identifier. The next column is a list of months in the date format %m/%d/%Y, and the next column is a date which indicates whether or not that person logged into the website during that month. What I want to generate is a 4th column that counts NULL values between non-NULL values for that 3rd column.

My question is: given the first 3 columns below, how would I write a query that generates the 4th column?

enter image description here

2
  • 3
    Tag you question with the DBMS you are actually using. The solution may vary by DBMS product.
    – Dan Guzman
    Jan 11 '20 at 18:49
  • 2
    Show SOURCE tables DDLs, sample data in them, and desured result for this data. As a fiddle or as scripts text, not as a picture. And specify ONE DBMS, including its version.
    – Akina
    Jan 11 '20 at 20:25
1

You could have a column which is the rolling count of the login column (because COUNT(Login) won’t include nulls. Then have a rolling count (less one) of that column, partitioned by it too (aka ROW_NUMBER()). This second column will reset to zero every time you have a login value, but will be able to keep increasing in between. All these should also be partitioned by the User, and you may want to do something special when COUNT(Login) is zero, because that will be the time before the user has logged in (so maybe use CASE for that).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.