Each User's First Touch

Kindled's behavioural analyst wants to know what each shopper did the moment they arrived — the very first thing the tracker recorded for them. The assumption everybody makes is that it is always the registration, and the point of the exercise is to find out whether that assumption survives contact with the data. Two actions by the same person can land on the same calendar day, in which case the lower row id is the earlier one.

events — one row per tracked action. user_id points at a row in users. event_type is one of signup, view_product, add_to_cart and purchase. event_date is the day the action happened. Nothing guarantees a person produces all four kinds, or any particular number of rows.

The table already exists in the database — there is nothing to create or load.

Task: Write a query that returns two columns, user_id and first_action, one row per person, first_action being the event_type of their earliest tracked action. Earliest means the smallest event_date, and if a person has two actions on the same day, the one with the smaller id. Rows come back in ascending user_id order.

Example output — shape only, on invented people. first_action holds whichever event_type happens to sit earliest for that person, so it need not be the same label for everybody: