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

Improve this question
2
  • 3
    Tag you question with the DBMS you are actually using. The solution may vary by DBMS product.
    – Dan Guzman
    Commented Jan 11, 2020 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
    Commented Jan 11, 2020 at 20:25

1 Answer 1

Reset to default
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).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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