@@ -786,8 +786,8 @@ GROUP BY utp.num, utp.template_ids, utp.name, utp.type, utp.display_name, utp.de
786
786
-- We do not start counting from 0 at the start_time. We check the last status change before the start_time for each user. As such,
787
787
-- the result shows the total number of users in each status on any particular day.
788
788
WITH
789
- -- dates_of_interest defines all points in time that are relevant to the query.
790
- -- It includes the start_time, all status changes, all deletions, and the end_time.
789
+ -- dates_of_interest defines all points in time that are relevant to the query.
790
+ -- It includes the start_time, all status changes, all deletions, and the end_time.
791
791
dates_of_interest AS (
792
792
SELECT @start_time::timestamptz AS date
793
793
@@ -809,9 +809,9 @@ dates_of_interest AS (
809
809
810
810
SELECT @end_time::timestamptz AS date
811
811
),
812
- -- latest_status_before_range defines the status of each user before the start_time.
813
- -- We do not include users who were deleted before the start_time. We use this to ensure that
814
- -- we correctly count users prior to the start_time for a complete graph.
812
+ -- latest_status_before_range defines the status of each user before the start_time.
813
+ -- We do not include users who were deleted before the start_time. We use this to ensure that
814
+ -- we correctly count users prior to the start_time for a complete graph.
815
815
latest_status_before_range AS (
816
816
SELECT
817
817
DISTINCT usc .user_id ,
@@ -823,10 +823,10 @@ latest_status_before_range AS (
823
823
AND (ud .user_id IS NULL OR ud .deleted_at > @start_time::timestamptz )
824
824
ORDER BY usc .user_id , usc .changed_at DESC
825
825
),
826
- -- status_changes_during_range defines the status of each user during the start_time and end_time.
827
- -- If a user is deleted during the time range, we count status changes prior to the deletion.
828
- -- Theoretically, it should probably not be possible to update the status of a deleted user, but we
829
- -- need to ensure that this is enforced, so that a change in business logic later does not break this graph.
826
+ -- status_changes_during_range defines the status of each user during the start_time and end_time.
827
+ -- If a user is deleted during the time range, we count status changes between the start_time and the deletion date .
828
+ -- Theoretically, it should probably not be possible to update the status of a deleted user, but we
829
+ -- need to ensure that this is enforced, so that a change in business logic later does not break this graph.
830
830
status_changes_during_range AS (
831
831
SELECT
832
832
usc .user_id ,
@@ -838,8 +838,8 @@ status_changes_during_range AS (
838
838
AND usc .changed_at <= @end_time::timestamptz
839
839
AND (ud .user_id IS NULL OR usc .changed_at < ud .deleted_at )
840
840
),
841
- -- relevant_status_changes defines the status of each user at any point in time.
842
- -- It includes the status of each user before the start_time, and the status of each user during the start_time and end_time.
841
+ -- relevant_status_changes defines the status of each user at any point in time.
842
+ -- It includes the status of each user before the start_time, and the status of each user during the start_time and end_time.
843
843
relevant_status_changes AS (
844
844
SELECT
845
845
user_id,
@@ -855,14 +855,14 @@ relevant_status_changes AS (
855
855
changed_at
856
856
FROM status_changes_during_range
857
857
),
858
- -- statuses defines all the distinct statuses that were present just before and during the time range.
859
- -- This is used to ensure that we have a series for every relevant status.
858
+ -- statuses defines all the distinct statuses that were present just before and during the time range.
859
+ -- This is used to ensure that we have a series for every relevant status.
860
860
statuses AS (
861
861
SELECT DISTINCT new_status FROM relevant_status_changes
862
862
),
863
- -- We only want to count the latest status change for each user on each date and then filter them by the relevant status.
864
- -- We use the row_number function to ensure that we only count the latest status change for each user on each date.
865
- -- We then filter the status changes by the relevant status in the final select statement below.
863
+ -- We only want to count the latest status change for each user on each date and then filter them by the relevant status.
864
+ -- We use the row_number function to ensure that we only count the latest status change for each user on each date.
865
+ -- We then filter the status changes by the relevant status in the final select statement below.
866
866
ranked_status_change_per_user_per_date AS (
867
867
SELECT
868
868
d .date ,
0 commit comments