How to get 100 records from a table with a value from year 2022 more than from year 2021

mr_adabala 0
Viewed 41

Here is the code that is working, but can it be more efficient and avoid subquery?

condition used: (revenue_2022 - revenue_2021) > 0 or revenue_2022 > revenue_2021

select
    id
from
    main_tbl
where 
    (
        (
        select
            revenue
        from
            main_tbl
        where
            id = ts.id
            and bq_year = 2022
            and revenue > 0
        ) - 
        (
        select
            revenue
        from
            main_tbl
        where
            id = ts.id
            and year = 2021
            and revenue > 0
        )
    ) > 0
limit 100

main_table:
id    | revenue | year
----------------------
1     | 4500    | 2022
1     | 4600    | 2021
2     | 3300    | 2022
3     | 5800    | 2022
3     | 5500    | 2021

expected output is the id 3 since its revenue for the year 2022 is greater than the year 2021

And 2 is not considered, since it doesn't have the year 2021 to compare with.

amazon-redshift performance subquery amazon-web-services sql
1 Answers

It's a little unclear what you'd like to do if there are other years, or more than one entry per id+year, but you could do something like:

select
  id
from main_table y2022, main_table y2021
where y2022.year = 2022
  and y2021.year = 2021
  and y2022.id = y2021.id
  and y2022.revenue > y2021.revenue
limit 100
John Rotenstein0
Related