Untitled

mail@pastecode.io avatar
unknown
pgsql
2 years ago
777 B
2
Indexable
Never
SELECT username,
       fullname,
    min(checkin_time) as checkin_time
FROM
  (SELECT users.username,
          users.fullname,
          min(action_data.current_execute AT TIME ZONE 'UTC' AT TIME ZONE 'ICT') AS "checkin_time"
   FROM action_data
   JOIN users ON users.wallet_address = action_data.wallet_address
   WHERE (action_data.current_execute AT TIME ZONE 'UTC' AT TIME ZONE 'ICT')::TIME >= '05:00:00'
     AND action_data.action_id = '7227d73c-4894-474c-b267-f8cd7355974f' -- check-in
     AND users.username in
       (SELECT code
        FROM visitors)
   GROUP BY users.username,
            users.fullname,
            date(action_data.current_execute AT TIME ZONE 'UTC' AT TIME ZONE 'ICT')) AS abc
GROUP BY username,
         fullname
ORDER BY min(checkin_time)