Buddy, For question 1 it was so simple that SELECT COUNT(1) FROM ORDER WHERE CAST(ORDER_DATETIME AS DATE) = CAST ( ORDER_DATETIME as DATE ) -1

Thanks for bringing this series it's very helpful 👍

Just a quick clarification: Shouldn't we use Sum instead of Count for the units sold in Questions 1 and 2? Thanks. 93 Days to go

How can you compare a day. It will not work for all use cases. Suppose the data set has an order from 14th of the previous month then your comparison extra(day from current_date()) will bring in that data as well.

4. Write a query to find the second earliest order_id for each customer for each date they placed 3 or more orders. Didn't this mean, query to fetch for each day a customer who has placed 3 or more orders???? no customer placed 3 orders a day.... In that case, the result set will be 0.

Your first answer seems wrong, they asked how many units ordered yesterday which means total qty ordered yesterday. count won't work here instead use sum, also where clause is wrong (Postgresql 16.0) select total_units from ( select order_datetime::date as dates, sum(order_quantity) as total_units, row_number() over(order by order_datetime::date desc) rn from orders group by dates)t1 where rn = 2 or select sum(order_quantity) as total_unit_sold from orders where order_datetime::date = (select max(order_datetime::date) - interval '1 Day' from orders) incase anyone attacks me, ill clarify why its wrong. firstly , WHERE EXTRACT(DAY FROM order_datetime) = EXTRACT (DAY FROM CURRENT_DATE) - 1 t this line of filtering is wrong because its comparing day with day - 1, but table has same day in 2024, 2023. so its counting last years same day qty aswell, when we just want yesterdays qty sold. secondly, counting count is wrong because it will given count of rows and not total qty sold. so sum(order_quantity) is more appropriate. thirdly, everyone will get different answer if you use zero analyst code cause he usses current_date which gives real time date. so answer depends on day you run the query. both code I gave passes all test case, and will works almost in all situations. also, Mu code will give answer for yesday according to table data and not real time. because realtime where wouldn't work. If you want code which will work realtime then use this. select sum(order_quantity) as total_unit_sold from orders WHERE order_datetime::date = CURRENT_DATE - 1 (do not use extract here)