PRESTO SQL: How to query value in max datetime in each txn and fill other that not select to null value

I have raw data that show in the first image and I want to select a max time for each user in po_time compared pr_time. Then select prepaid or postpaid, if you select prepaid and the postpaid should be NULL

Thanks

enter image description here

1 answer

  • answered 2019-11-19 08:50 Tim Biegeleisen

    Try this query:

    SELECT
        uid,
        phone_number,
        time,
        CASE WHEN pr_time > po_time THEN telecom_prepaid END AS telecom_prepaid,
        CASE WHEN po_time > pr_time THEN telecom_postpaid END AS telecom_postpaid
    FROM yourTable
    ORDER BY
        uid;