Last value with max(date)

Question

I wanna find out the total number of my app users who are using the app from ios and who are using android, but I'm not interested in all time I just want to know the last value. So, I know that I'll be using lastvalue and max(date) but I'm facing some errors while running the code. I'll be sharing the code here:

SELECT last_value(DEVICE_TYPE) OVER (PARTITION BY CREATED_AT ORDER BY CREATED_AT) AS Device, date(max(CREATED_AT)) FROM USER_DEVICES
GROUP BY DEVICE_TYPE, CREATED_AT

Answer 1

Interesting..

I created a CTE with some dummy data to see if the code generates errors etc, etc:

WITH USER_DEVICES AS (
    SELECT * FROM VALUES
    ('Apple', '2022-01-01'::date),
    ('Apple', '2022-01-02'::date),
    ('Andriod', '2022-02-01'::date),
    ('Andriod', '2022-02-02'::date)
    t(DEVICE_TYPE, CREATED_AT)
)

SELECT 
    last_value(DEVICE_TYPE) OVER (PARTITION BY CREATED_AT ORDER BY CREATED_AT) AS Device, 
    date(max(CREATED_AT)) 
FROM USER_DEVICES
GROUP BY DEVICE_TYPE, CREATED_AT;

and it run fine, and gives:

DEVICE	DATE(MAX(CREATED_AT))
Apple	2022-01-01
Apple	2022-01-02
Andriod	2022-02-01
Andriod	2022-02-02

So the date(max(CREATED_AT)) is finding the last create_at and then casting that to a date again. Which the last bit is not needed, so it could be just max(CREATED_AT)

But your last_value is partition'ed by the same value that is ordering those partitions, so you will just get "all results"

So given all that, lets re-read your question, and try find the true intent and guess the code...

I wanna find out the total number of my app users who are using the app from ios and who are using android

makes me think you want to group per day, and do some counting..

WITH USER_DEVICES AS (
    SELECT * FROM VALUES
    ('Apple', '2022-01-01'::date),
    ('Apple', '2022-01-01'::date),
    ('Andriod', '2022-02-01'::date),
    ('Andriod', '2022-02-01'::date)
    t(DEVICE_TYPE, CREATED_AT)
)
SELECT
    CREATED_AT,
    count_if(DEVICE_TYPE = 'Apple') as apple_count,
    count_if(DEVICE_TYPE = 'Andriod') as andriod_count
FROM USER_DEVICES
GROUP BY CREATED_AT;

which gives:

CREATED_AT	APPLE_COUNT	ANDRIOD_COUNT
2022-01-01	2	0
2022-02-01	0	2

but I'm not interested in all time I just want to know the last value

so I assume you mean you want just the result for the most resent day.

Which can be done via a QUALIFY

SELECT
    CREATED_AT,
    count_if(DEVICE_TYPE = 'Apple') as apple_count,
    count_if(DEVICE_TYPE = 'Andriod') as andriod_count
FROM USER_DEVICES
GROUP BY CREATED_AT
QUALIFY ROW_NUMBER() OVER (ORDER BY CREATED_AT DESC) = 1;

CREATED_AT	APPLE_COUNT	ANDRIOD_COUNT
2022-02-01	0	2

Answer 2

If I understood you right-- for each user, you want to include the device type associated with the date of most recent app activity for that user before you aggregate users per device type.

with cte as 
(select *, row_number() over(partition by user_id order by created_at desc) as rn
 from user_devices)
 
 
select device_type, count(*) as counts
from cte
where rn=1
group by device_type;