I have a "user_activity_log" table that contains the fields "id", "client_id", "hitdatetime", and "action".
id | client_id | hitdatetime | action |
---|---|---|---|
2661715 | 17 | 2020-09-18 11:30:43 | visit |
2661716 | 17 | 2020-09-18 11:30:54 | registration |
2661717 | 17 | 2020-09-18 11:31:16 | visit |
It is necessary to output:
- "client_id", from the input table
- "visit_dt", that is associated to the "hitdatetime" field when the "action" equals to
'visit'
, otherwise it is null - "is_registration", that is associated to
1
if "action" equals to'registration'
, otherwise it is0
The CASE
statement is mandatory for this query.
I've started writing the query, but I don't know what to put in place of the signs ???
.
SELECT client_id,
CASE WHEN action = 'visit' THEN ??? ELSE 'NULL' END as visit_dt,
CASE WHEN action = 'registration' THEN '1' ELSE '0' END as is_registration
FROM user_activity_log;
Can you provide help?