I simplified the ask to be honest. It was not a simple `select date, country, sum(gdp) from table group by 1,2`. There were additional components to the question.
And as you mentioned, you can also distinct on the window function. This is not an inefficient operation in Redshift.
Also I fundamentally disagree with your premise. One result set is equivalent to another, and as long as you aren't using unnecessary nested loop joins or such, there are a dozen ways to solve a query.
In another interview at a similarly-competitive company to AWS , I was asked to do some work on orders and orderdetail tables to find all customers where the aggregate total of their orders from 2005 were greater than that of their orders from 2007. I did it using a couple of `sum(case when extract(year from datecol) = 200x then order_amount end)` statements.
The interviewer told me the question was intended to test whether the applicant was able to do a self-join but they were impressed with my alternative solution. That is the proper way to test SQL, not to say someone "did the query the wrong way"
And as you mentioned, you can also distinct on the window function. This is not an inefficient operation in Redshift.
Also I fundamentally disagree with your premise. One result set is equivalent to another, and as long as you aren't using unnecessary nested loop joins or such, there are a dozen ways to solve a query.
In another interview at a similarly-competitive company to AWS , I was asked to do some work on orders and orderdetail tables to find all customers where the aggregate total of their orders from 2005 were greater than that of their orders from 2007. I did it using a couple of `sum(case when extract(year from datecol) = 200x then order_amount end)` statements.
The interviewer told me the question was intended to test whether the applicant was able to do a self-join but they were impressed with my alternative solution. That is the proper way to test SQL, not to say someone "did the query the wrong way"