I interviewed with AWS for an analyst/business intelligence engineer role.
When asked to write a query to get the total GDP by month per country, I wrote a query along the lines of:
select date_trunc('month',datecolumn) as month, country, sum(GDP) over (partition by date_trunc('month', datecolumn), country)
FROM the_table
order by 1
The interviewer asked me if I was missing something. I said no. "Don't you need an additional clause if you're doing a sum?"
"No," I responded, "in AWS Redshift (and postgres and mysql 8.0+ and...) if you do a Sum Window Function a group by is not necessary"
The interviewer made a noise and demeanor changed as if I knew not what I was talking about. While I can't be 100% sure, I'm almost positive I was failed for "missing" the group by.
I went home later that day and wrote exactly the same query, which ran successfully. The AWS BI Intelligence Engineering manager didn't even know how to use a sum window function. I was a bit frustrated at being failed because the interviewer didn't know of a (well-known, well-used) feature, but perhaps I dodged a bullet.
When asked to write a query to get the total GDP by month per country, I wrote a query along the lines of:
select date_trunc('month',datecolumn) as month, country, sum(GDP) over (partition by date_trunc('month', datecolumn), country) FROM the_table order by 1
The interviewer asked me if I was missing something. I said no. "Don't you need an additional clause if you're doing a sum?"
"No," I responded, "in AWS Redshift (and postgres and mysql 8.0+ and...) if you do a Sum Window Function a group by is not necessary"
The interviewer made a noise and demeanor changed as if I knew not what I was talking about. While I can't be 100% sure, I'm almost positive I was failed for "missing" the group by.
I went home later that day and wrote exactly the same query, which ran successfully. The AWS BI Intelligence Engineering manager didn't even know how to use a sum window function. I was a bit frustrated at being failed because the interviewer didn't know of a (well-known, well-used) feature, but perhaps I dodged a bullet.