Redshift query to find unique users

Thank you for your response!

Your solution has been helpful in removing duplicate amplitude_ids. However, there still remains a discrepancy between the unique users identified by the Amplitude segmentation board and the results from my Redshift query.

Here is my refined query that eliminates duplicate users. It has been successful in identifying and removing approximately 80% of the duplicated users.

select a.user_id, a.amplitude_id  
from events_1234 a
        left join merge_ids_1234 b on a.amplitude_id = b.amplitude_id
WHERE 
      DATE(event_time) between '2023-01-01' and '2023-01-07'
      AND platform IN ('iOS', 'Android')
      and user_id is not null
group by a.user_id, a.amplitude_id 

Maybe I missed unmerged amplitude_ids. How can I find that?

I've analyzed the unique user data from Amplitude by following the steps below:

1. Obtained user information from the Segmentation dashboard by clicking the 'Download Users' button.
2. Exported the query results from Redshift.
3. Compared the Amplitude IDs from both datasets to identify any discrepancies or overlaps.

These steps helped me to effectively evaluate the unique users in Amplitude and cross-reference them with the query results from Redshift.