0

Nice to meet you, dear community! I want to select users from several tables who performed their last event not later than 7 days since registration day and group them by their start version.

However, the number of select users is quite low, could you please tell me where I have a mistake?

FROM   (SELECT user_id, country, user_creation_time,  event_type, event_time, start_version 
        FROM   PUBLIC.export_07_2020 
        UNION 
        SELECT user_id, country, user_creation_time,  event_type, event_time, start_version
        FROM   PUBLIC.export_08_2020 
        UNION 
        SELECT user_id, country, user_creation_time,  event_type, event_time, start_version
        FROM   PUBLIC.export_09_2020) dat 
WHERE dat.country = 'United States' 
       AND dat.user_creation_time BETWEEN 
           '2020-07-01 00:00:00' AND '2020-09-23 23:59:59' 
      AND NOT EXISTS (SELECT dit.user_id 
                   FROM   (SELECT user_id, 
                                  country, 
                                  user_creation_time, 
                                  event_type, 
                                  event_time 
                           FROM   PUBLIC.export_07_2020 
                           UNION 
                           SELECT user_id, 
                                  country, 
                                  user_creation_time, 
                                  event_type, 
                                  event_time 
                           FROM   PUBLIC.export_08_2020 
                           UNION 
                           SELECT user_id, 
                                  country, 
                                  user_creation_time, 
                                  event_type, 
                                  event_time 
                           FROM   PUBLIC.export_09_2020) dit 
                   WHERE  dat.user_id = dit.user_id 
                          AND Greatest(dit.event_time) > 
                              Dateadd(day, 7, dit.user_creation_time))  
GROUP  BY dat.start_version```
Liliia Lutsenko
  • 1
  • 1
  • Please provide ample data and desired results. Your quest is much, much simpler than the query you have shown. – Gordon Linoff Jan 17 '21 at 12:40
  • In the tables (each table = 1 month) I have column with user ids and column with timestamps when they performed their events (1 row = 1 timestamp). I need to count only those users who performed their last event earlier than 7 days since registration day. – Liliia Lutsenko Jan 17 '21 at 13:13
  • and group these users by the app version when they registered – Liliia Lutsenko Jan 17 '21 at 13:28

0 Answers0