4 ms·

To be fair, in my opinion, you chose the wrong tool for the task. Why would you use a sum window function instead of a much simpler aggregate sum function (that

To be fair, in my opinion, you chose the wrong tool for the task. Why would you use a sum window function instead of a much simpler aggregate sum function (that does require a group by)? Without the group by, you will be returning every row from the table, which isn't what the task calls for. You'd then have to do an additional row_number() or distinct or something to just get the unique rows per month per country.

oarabbus_ 7y ago
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"
- jaf656s 7y ago
  >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. If they consume similar resources I'd almost always opt for the simplest one to understand. In cases where the simpler one is less efficient, I might still use it if the task didn't require absolute optimization.
  - SilasX 7y ago
    Sure, and that would be a fair criticism, to say, "is there a version of this that's more intuitive and easier to read with the same load on the server?" But the interviewer (assuming the OP faithfully represented the exchange and the domain) is insisting that the query does not do what was asked -- when it does -- and is saying so based on the interviewer's own incomplete understanding. That's not a good reason to reject a candidate, even if there might be other good reasons in this case.
    - oarabbus_ 7y ago
      Exactly, it would have been fair game to ask why I used a window function instead of an aggregate function + subquery joined back onto the main result set. Or, to ask if I could rewrite the query another way which would be more efficient. However, the interviewer didn't simply insist the query does not do what was asked (when it did indeed return the correct results). Instead, the interviewer actually insisted it was an invalid query which would not run at all.
      - jaf656s 7y ago
        In this case it sounds like a bad interview and that they missed a good opportunity for discussion!
      - socialist_coder 7y ago
        From their perspective, they have hundreds of candidates and need to quickly filter a bunch out. So, having low-technical knowledge recruiters ask questions and look for a standard set of answers is the way they do it. There is no room for technical discussion during this phase, no room for nuance. I'm not saying this is a good process or that it's fair, but it is the reality right now. You still get OK results and it's far faster and cheaper.
        oarabbus_ 7y ago
        I’d already passed the recruiter screen and another phone interview. This was the technical interview with the manager of the group.

oarabbus_ 7y ago

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"

jaf656s 7y ago
>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. If they consume similar resources I'd almost always opt for the simplest one to understand. In cases where the simpler one is less efficient, I might still use it if the task didn't require absolute optimization.
- SilasX 7y ago
  Sure, and that would be a fair criticism, to say, "is there a version of this that's more intuitive and easier to read with the same load on the server?" But the interviewer (assuming the OP faithfully represented the exchange and the domain) is insisting that the query does not do what was asked -- when it does -- and is saying so based on the interviewer's own incomplete understanding. That's not a good reason to reject a candidate, even if there might be other good reasons in this case.
  - oarabbus_ 7y ago
    Exactly, it would have been fair game to ask why I used a window function instead of an aggregate function + subquery joined back onto the main result set. Or, to ask if I could rewrite the query another way which would be more efficient. However, the interviewer didn't simply insist the query does not do what was asked (when it did indeed return the correct results). Instead, the interviewer actually insisted it was an invalid query which would not run at all.
    - jaf656s 7y ago
      In this case it sounds like a bad interview and that they missed a good opportunity for discussion!
    - socialist_coder 7y ago
      From their perspective, they have hundreds of candidates and need to quickly filter a bunch out. So, having low-technical knowledge recruiters ask questions and look for a standard set of answers is the way they do it. There is no room for technical discussion during this phase, no room for nuance. I'm not saying this is a good process or that it's fair, but it is the reality right now. You still get OK results and it's far faster and cheaper.
      - oarabbus_ 7y ago
        I’d already passed the recruiter screen and another phone interview. This was the technical interview with the manager of the group.