Find dates with lower revenue than yesterday

Question

I'm fairly new to BQ SQL and am stuck on a query regarding dates. I have a table that consists of customer_id (int), date_purchase (date), sales (int).

The query is to find all customer_id and date_purchase with lower sales compared to the previous date_purchase (yesterday).

I've tried many things but not sure how to execute the code to work. I also could not code to automatically identify today and yesterday's date, so have manually input it. Below are sections that I think need to be put together but I could be wrong.

select id, revenue 
from (
    SELECT id, date_purchase as today, revenue 
    from SALES 
    where date_purchase>='2022-05-29'
) t1 
-- unsure how to join these
(
    SELECT id, date_purchase as yesterday, revenue 
    from SALES 
    where datepurchase='2022-05-28'
)t2 on t1.id=t2.id 
where t1.revenue<t2.revenue

Can anyone help me with this? Thanks

Answer 1

Literally, where the -- unsure how to join these comment is located, simply replace that with join.

Also, in the outer select, need to specify which specific columns from the t1 or t2 aliases to avoid ambiguity, because both derived table queries (in the from clause) have id and revenue references. (alternatively, you could rename these columns within the selects like was done with the data_purchase column to avoid ambiguity)

Example: (I've assumed the data from t1 is desired in the select)

select t1.id, t1.revenue
from (
    SELECT id, date_purchase as today, revenue 
    from SALES 
    where date_purchase >= '2022-05-29'
) t1 
join
(
    SELECT id, date_purchase as yesterday, revenue 
    from SALES 
    where date_purchase = '2022-05-28'
) t2 on t1.id = t2.id 
where t1.revenue < t2.revenue;

Using this sample data:

Gives this output:

Example DB fiddle

Though it may be a new concept to you, know that a self-join of the SALES table can achieve the same results with less SQL syntax. I added a self-join example as the last query in the DB fiddle.