Good tutorial. Thanks. For the 3rd query though, I think we could have added the 2nd argument (first_transaction_DT) to the first expression (Rank) itself. instead of creating 2 separate fields. -- rank() over (partition by user_id, payment_method order by payment_counts desc, first_transaction) payment_rnk
[Update] The founder was kind enough to reach out directly and resolve the issues, really appreciate it. I know they are a small team to get the product start and running, so do expect issues as it won't be perfect in the beginning, but also have patience and reach out to Dan if you have questions/suggestions. [original text] There are several mistakes in the solution across questions. I've submitted feedback but never heard back nor are the mistake corrected. This is a bit concerning. I would wait for the product to become more stable to subscribe or it would actually take you more time to find out why your solution does not work.
The question asks for product names with the highest price per category. If you just apply max, you will only get the price, not the corresponding product.
@@DataInterview that's not exactly true, if you include product name and id in the group by, it will function just as normal as their will be no ties to break, you can actually use group by in that sense
In the hard question part, some maild ids are left out... consider the example: maild_id trans_method trans_count minimum_date purchase_rank purchase_order email_id 1 credit_card 4 jan-10-2022 1 2 email_id 1 gifct_c 1 jan-11-2021 2 1 please provide a solution for one such uc....
Good tutorial. Thanks.
For the 3rd query though, I think we could have added the 2nd argument (first_transaction_DT) to the first expression (Rank) itself. instead of creating 2 separate fields.
-- rank() over (partition by user_id, payment_method order by payment_counts desc, first_transaction) payment_rnk
Maybe showing the output for each step will be easier to understand and also giving the create table query will be helpful.
Which platform are you using
[Update]
The founder was kind enough to reach out directly and resolve the issues, really appreciate it. I know they are a small team to get the product start and running, so do expect issues as it won't be perfect in the beginning, but also have patience and reach out to Dan if you have questions/suggestions.
[original text]
There are several mistakes in the solution across questions. I've submitted feedback but never heard back nor are the mistake corrected. This is a bit concerning. I would wait for the product to become more stable to subscribe or it would actually take you more time to find out why your solution does not work.
Why for the second question we use subquery instead of using joins and put filter in the end?
Is the expected output usually showed in the interviews?
I like this new logo and video intro. Thank you for walking us through more SQL questions
Of course :)
Ace SQL interviews with 👉 daetama.io/ 🚀 Use the promo code: DATAINTERVIEW10 to get 10% off the monthly subscription.
LAUNCH50 doesn't work. It shows 'This code has expired.' Do you have other promo code?
As far as I know, there're no sample tables/dataset shown in an actual interview, but only columns, right?
I’d say that depends on the interviewer. Some clients only had the schema info. Others had sample tables.
I had a couple sample records from each table in mine
For the first question, why not just group by category & product name with MAX aggregate on price?
The question asks for product names with the highest price per category. If you just apply max, you will only get the price, not the corresponding product.
@@DataInterview that's not exactly true, if you include product name and id in the group by, it will function just as normal as their will be no ties to break, you can actually use group by in that sense
very good video, the problem is that the instructor looks really really like my ex...
The last one was really difficult
The solution of last questions is incorrect
紫配黑色 太费眼睛了
In the hard question part,
some maild ids are left out...
consider the
example:
maild_id trans_method trans_count minimum_date purchase_rank purchase_order
email_id 1 credit_card 4 jan-10-2022 1 2
email_id 1 gifct_c 1 jan-11-2021 2 1
please provide a solution for one such uc....
change the query with rank() to : rank() over(partition by user_id order by count(*) DESC, min(transaction_dt) ASC)
exactly, the solution is incorrect! That's really confusing