Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SELECT
uid,
max(date_sequence_count) as max_date_sequence_count
FROM (
SELECT
uid,
COUNT(date_sequence_id) + 1 AS date_sequence_count
FROM (
-- same rank for sequence
SELECT
uid,
RANK() OVER(ORDER BY uid, linked_with_prev_date) AS date_sequence_id
FROM (
-- calc new feature 'linked with prev date'
SELECT
uid,
(ndate - (LAG(ndate) OVER(PARTITION BY uid ORDER BY ndate)) = '1 day') AS linked_with_prev_date
FROM (
-- decrease precision
SELECT DISTINCT
uid,
date_trunc('day', date) "ndate"
FROM
posts
) AS p2
) AS p3
WHERE linked_with_prev_date
) AS p4
GROUP BY
uid,
date_sequence_id
) AS p5
GROUP BY
uid
@Ruzzz

This comment has been minimized.

Copy link
Owner Author

@Ruzzz Ruzzz commented Dec 9, 2019

Calculate the rating of the most active users based on the longest streak of posts. The size of the streak for the user is the number of consecutive time intervals in which at least one post has been published by this user.

For example, if the user created posts on:

  • 01 Sept. at 12:00,
  • 02 Sept. at 14:00,
  • 03 Sept. at 9:00,
  • and 05 Sept. at 10:00

Then at the interval size of 1 day, we can say that the longest streak of posts for this user is 3. In other words, the user made at least one post for 3 consecutive days.

The interval should be passed as a query parameter and can be 24h or 1h.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.