Btw I still HIGHLY recommend you get a whiteboard! I can make an entire video about why I think whiteboards are a data scientist's best friend haha. In any case, I hope this video is helpful for people doing quarantine interviews! When things get better (and if you guys like this type of video) I will go back to my beloved whiteboard 😂
Thank you for making this! I use SQL in my current job and I was wondering what types of SQL knowledge a potential employer would be looking for. This was really helpful!
I just started reviewing SQL yesterday after like a 2+ year IT/programming hiatus and got super excited after getting a decent chunk of the logic right :D no clue about timestamps and how to transform them though, so that's something I need to look further into. Great stuff, keep it up!
For someone who lives and works in the Bay Area, this is SO RELEVANT! It is helping me immensely in my journey and the best part is that this very closely mimics technical SQL interview questions you will receive at tech companies here. Thank you and keep posting this type of content please! :)
Hi Tina, I want to thank you again. I’m a CS student in mainland China, and constantly meet sexist comments such as “Women are naturally not good at tech as men”. I could be easily influenced by these kind of comments and started to question myself. But every time I watched your video about CS and tech, I feel confident and empowered as a woman again. Thank you so much Tina❤I wish you all the best
This is amazing ! Thank you so much to make this kind of video! I especially like the way that you lay out all the assumptions before you writing the query!
Hello Tina! I am super excited for your voice on behalf of women in the DS field and Asian as well. So loved the way you support beginners in the field.
Aww thank you!! Wow that would be a honour! I'm still learning so much every single day and hope that I can share the experience and knowledge I've gathered, as well as the new things I'm learning 😊
Very good explanation, at least as I am in transitioning to DS field, your content really help me to build those logic. I love it and looking forward to see you post more video on this topic. Well done & good luck :)
Hi Tina. I love your SQL videos. Thanks a ton for your channel and all the great content. I wanted to point out what I think might be a missing assumption for the solution presented in this problem. I think using the DATE_TRUNC() function here requires the assumption of all the logs occurring within the same month. Unless I'm mistaken, that function used across multiple months, would aggregate the 1st of January, 1st of February, 1st of March, etc. into day 1. If we assume the logs span a greater segment of time, such as a year, then another function may be needed in a sub-query or CTE. The DATEDIFF function could be used to get the difference between two timestamps, and round the output to days - example: DATEDIFF(dd, i.ts, w.ts) AS DaySpan - Then we could filter results to where DaySpan = 0.
But DATE_TRUNC with day includes the month and the year, so I don't think this is an issue! It's truncating to the day (so removing time info but not the year or month), instead of extracting the day on its own. If you used EXTRACT('day' from ts) then yes you would definitely have that issue!
I'm a final year undergraduate in computer science and I was really confused what should I do, what languages I should learn but after I discovered your channel and subscribed it ofc I've decided to be a data scientist. Thank you so much for encouraging me. I just want to let you know that your videos have so much impact on your audience and please stay motivated for us and upload more video like this. Love you
Hey Rhea! Glad to hear that you are considering a career in data science! Im a fellow fan of tinas videos (so helpful!) and small RUclipsr - I’ve got some videos up on my channel on my work and career tips as a data scientist, maybe they’d help you out? 🙂
@@candidlyvivian Hi candidly Thank you for your kind works and of course I'll check out your account right away and you got yourself a new subscriber too:)
"Let me know if I'm repeating myself repeatedly" 👌🏼Love the humor! Keep up these videos, they are super useful and your delivery is great - clear and easy to connect with
Tina you are a breath of fresh air. Very precise presentation, perfect pronounciation in English, just the right length of the video. Keep the sql videos coming. Well done.
1. start off with making assumptions 2. write steps to what you need to do (1.join 2.match by day …) 3. outline what a final output look like 4. code 5. review 6.optimize
Great video and I like this live coding style. If you continue live coding SQL/at all could you go through SQL beginner, intermediate, and advanced techniques? But, all that said, I really enjoy these videos.
Big thumbs up!! Very clearly explained. Really enjoyed it! It would be really appreciated if you could also make some videos to talk about what the real live SQL questions look like, because it seems to me that those real questions are quite different from Leetcode SQL questions (e.g. sometimes we need to come up with metrics to select ourselves, sometimes there is no sample result preview, etc )
Hi! Yup now that I’ve been working for a while, the actual hard part for tackling a SQL question is figuring out what the question even is + finding data. Also there’s the constant paranoia of whether your query is doing what you think it’s doing since there’s obviously no answer key D: I should definitely make a video on this topic! Thanks for suggesting!!
this is good but i'll recommend covering basic data structure and leetcode type questions essential for data science interviews since not many youtubers have covered that and also since you have experienced the same in your interview :) amazing video as always;liked ..
Yeah, someone else mentioned that too! Okay - will do some more research on what exactly companies are asking these days and draft for a future video. For my own data science interview, there were actually no algorithm questions asked. Although I was asked plenty of leetcode type questions for my SWE interviews so I do have some experience doing them.
@@TinaHuang1 oh is that so?no leetcode types(be it easy) questions asked for data science interview ? ok then you can continue making the videos in the same way then ! also it'll be really good video idea where you share your interview exp as a data scientist for the untitled company :) oh and here is one reference interview exp with amazon for data scientist role leetcode.com/discuss/interview-experience/324656/Amazon-or-Data-science-Intern-or-Seattle-Offer
It's a nice video. I think your approach to the problem could be a little better. The goal should be to find three pieces of information. The count of those who only signed in from phone, those who only signed in from web, and those that did both. The answer to the question is the count of those that do both. But the veracity of the answer is dependent upon all three. An interviewer can be asking multilayered questions. You established your ability to write the query. You may have missed the opportunity to demonstrate your analytical skill.
Thank you! ❤️❤️ it really doesn’t matter which kind, they don’t care as long as it’s not something wayyy obscure. I prefer Postgresql because it has more handy functions!
Wow! What an effective and thorough video... I will def be watching more. Which application are you using to store your code? I’ve attempted to keep a repository of useful code I always go back to but vscode is not good for that. The interface you’re using looks way better.
Better do aggregation before table joining, or simply use union instead of joining for this scenario. Log tables usually are huge, even after partitioned.
Awesome! I recently bought a whiteboard to practice LeetCodes but I guess digital practice is even more important now. Also congrats on the rapid growth in the past days, I'm sure your channel will take off even more! 🚀
Yeah - very sad but impossible to avoid because COVID is ruining everything 😢 I think you mentioned that you just started university though so you'll still have plenty of chances to whiteboard for the coming years haha. And thank you!! You've been so amazingly supportive since day 1 on this channel and I'm so grateful 🥰
True! Yes I just finished my first semester so I probably get to see both sides of the coin :D But it's just so exciting I can't wait to start haha! I'm glad you appreciate it!! Your videos have been really helpful and I wanted to give back a bit of that positive and helpful energy! Keep going! 😁
Should we not pre-aggregate the tables to day frequency before joining? The result would be the same but would it not be more optimized since the number of rows will decrease for both tables before joining?
Hey this was pretty simple for me and I am not a data scientist nor an engineer. I used codecademy course on sql, learned it in about a week. I highly recommend it.
Yup - the actual questions themselves are really not very hard at all :) what’s the most important for these interviews is your ability to communicate and think critically/course correct when challenged
Hello I Tina. I liked and subscribed. Do you have an option for Date aswell ? Jk. Keep the content coming. If you publish any short courses I would totally buy that. Great video clear explaining 👍
Great explanation, but I believe the query is false, you need to specify a "full join" instead of just "join" Because in your query if one user exists in just one table (web or iPhone) it won't be selected (inner join) Cheers
Same here, instead of matching to multiple keys in the join where ts is probably non-indexed, you can directly aggregate twice after a union to arrive at the same answer
Hi! I got these questions from Glassdoor but in my other SQL Sunday videos i get them from scratascratch, which I have an affiliate link for in my other SQL Sunday videos (at no cost to you) if you wanna check that out :)
two things: 1. would date diffing on i.ts and w.ts less than 24 to catch global clocks have been bonus points or outside of the question? 2. is GROUP BY 1 short hand?
1. outside of the question, since the time would be server time which sould just be in one timezone 2. Group by 1 is group by first column, so yes shorthand She could have also used USING(id) instead of ON...
In my view it will be more meaningfull to create a session table rather than iPhone and web. The session table should contain a column to represent the client type.
Hello Tina, thanks for the video it is really useful. in this questions you used pg instead of mysql and i wonder during the interview, did you got to choose which you want to use?
Hi! Yup I prefer Postgres because it has way more handy functions. Companies don’t care which sql you use usually unless it’s some really weird and rare one haha
Tina , I think you can have another column with date data type and use that date data type column in select where and group by because if we think that table has millions or billions of records i think date_trunc will affect performance negatively, please let me know your thoughts, thanks.
What do you do your GROUP BY with 1 and not with the non aggregate columns of the Select statement, as the aggregate functions syntax requires it? I do not understand.
Can we also use an intersect instead ? with used_both as ( Select distinct date, user from iphone intersect Select distinct date, user from web ) select u.date, count(u.user) from used_both u group by u.date order by u.date asc
I always encourage people to try it out 😉 it’s the best way to learn! Also tbh it’s been a while since I’ve done this question and don’t exactly remember 🙈
good job, but this query will not scale with number of sessions. E.g. imagine edge case when one day has 1 billion sessions from 100 users. It will be much more optimal to select unique users from each table, then join, then count.
Hi Tina, thanks for sharing this. I have a small question (might be stupid): if some users log in several times both from iPhone or web, I mean 'multi to multi relationships', could we still use these queries? Or should we make any changes? Thanks so much!
Hello stella, I just tried this case out (same user logs in multiple times on both iPhone and web in one day) and the user is still only counted once. I believe this is because of the DISTINCT statement. On a given day each user can only be counted once.
Tina, this is awesome. Really like your videos here. I am exactly in line with you, I started my channel to try to help people in the career of data as well. Cheers.
Hi Tina ! Thanks for your content . I have a solution with WHERE instead of JOIN to join the tables. In your opinion which one is the best in term of optimization? and why ? thks:)
It's hard to say. It's really depending on the database you are using, the amount of data you are running your sql against and the tables structures, but usually the JOIN is faster, because the databases have several algorithms for performing the joins (hash join for example is faster when you are dealing with small tables, while merge join is faster when you are dealing with large tables), while the where clause depends on the structure of the table, like does have indexes, are the statistics valid, is the index created on the column in where clause...and the optimizer may or may not chose to use that information in the where clause and just do a full table scan, which is something you tend to avoid...
You should check out azure data studio is a free Microsoft sql ide. But it only support sql server and postgress. Also Support notebooks with python or sql Kernel.
Can you please explain Why have you used join on date column here ,Is it needed? and how results would have been different if you skip date condition ?
My only question is the assumption about the inner join of the iPhone and web logs. Wouldn't it be more logical if the entity were concerned with customer activity from any source and the "test" for the interviewee would be to consolidate customer activity from any source and then count for "uniqueness"?
Btw I still HIGHLY recommend you get a whiteboard! I can make an entire video about why I think whiteboards are a data scientist's best friend haha.
In any case, I hope this video is helpful for people doing quarantine interviews! When things get better (and if you guys like this type of video) I will go back to my beloved whiteboard 😂
ruclips.net/video/i2BjvSNJprA/видео.html
@@jessicajimmy5171 : Why didn't you consider writing an inner query as that's faster than the one you wrote?
I had a doubt after watching you video. If you are using date_trunc function why does the output show values as timestamp?
I would like that video, thank you!
Do you mean any tool or normal white board which they use in college.
What's beautiful about this video is that it doesn't just show the solution but the technique of how to approach the question to create better queries
Thank you for making this! I use SQL in my current job and I was wondering what types of SQL knowledge a potential employer would be looking for. This was really helpful!
I just started reviewing SQL yesterday after like a 2+ year IT/programming hiatus and got super excited after getting a decent chunk of the logic right :D no clue about timestamps and how to transform them though, so that's something I need to look further into.
Great stuff, keep it up!
For someone who lives and works in the Bay Area, this is SO RELEVANT! It is helping me immensely in my journey and the best part is that this very closely mimics technical SQL interview questions you will receive at tech companies here. Thank you and keep posting this type of content please! :)
Yess I try to follow the tech interview structure as much as possible! Yay I’m so glad you’re enjoying them ❤️
this was so helpful, actually the most straight forward question+answer tutorial i've ever seen
Tina I just want to say thank you A LOT!!!!!
I am so glad to see how to actually process a question!
Hi Tina, I want to thank you again. I’m a CS student in mainland China, and constantly meet sexist comments such as “Women are naturally not good at tech as men”. I could be easily influenced by these kind of comments and started to question myself. But every time I watched your video about CS and tech, I feel confident and empowered as a woman again. Thank you so much Tina❤I wish you all the best
This is amazing ! Thank you so much to make this kind of video! I especially like the way that you lay out all the assumptions before you writing the query!
I'm going through your Sql Mock Interview series. It's cool seeing a preview of what the Interviews would look like!
Thank you...... Your work ethic inspires me
Hello Tina! I am super excited for your voice on behalf of women in the DS field and Asian as well. So loved the way you support beginners in the field.
Aww thank you!! Wow that would be a honour! I'm still learning so much every single day and hope that I can share the experience and knowledge I've gathered, as well as the new things I'm learning 😊
@@TinaHuang1 very encouraging! Have a nice weekend :)
Very good explanation, at least as I am in transitioning to DS field, your content really help me to build those logic. I love it and looking forward to see you post more video on this topic. Well done & good luck :)
I'm so happy you found it helpful!! Alright, will make more videos like these 😄
ruclips.net/video/i2BjvSNJprA/видео.html
Hi Tina. I love your SQL videos. Thanks a ton for your channel and all the great content. I wanted to point out what I think might be a missing assumption for the solution presented in this problem. I think using the DATE_TRUNC() function here requires the assumption of all the logs occurring within the same month. Unless I'm mistaken, that function used across multiple months, would aggregate the 1st of January, 1st of February, 1st of March, etc. into day 1. If we assume the logs span a greater segment of time, such as a year, then another function may be needed in a sub-query or CTE. The DATEDIFF function could be used to get the difference between two timestamps, and round the output to days - example: DATEDIFF(dd, i.ts, w.ts) AS DaySpan - Then we could filter results to where DaySpan = 0.
Ohh nice catch!!
Or you could just CAST the datetime to a date. CAST(i.ts AS date), same for the join.
But DATE_TRUNC with day includes the month and the year, so I don't think this is an issue! It's truncating to the day (so removing time info but not the year or month), instead of extracting the day on its own.
If you used EXTRACT('day' from ts) then yes you would definitely have that issue!
I'm a final year undergraduate in computer science and I was really confused what should I do, what languages I should learn but after I discovered your channel and subscribed it ofc I've decided to be a data scientist. Thank you so much for encouraging me. I just want to let you know that your videos have so much impact on your audience and please stay motivated for us and upload more video like this. Love you
Hey Rhea! Glad to hear that you are considering a career in data science! Im a fellow fan of tinas videos (so helpful!) and small RUclipsr - I’ve got some videos up on my channel on my work and career tips as a data scientist, maybe they’d help you out? 🙂
@@candidlyvivian Hi candidly Thank you for your kind works and of course I'll check out your account right away and you got yourself a new subscriber too:)
How is it going with your career so far as a date scientist I’m in a similar situation final year and unsure what path to take
This was really smooth haha-I learned a lot. Thanks Tina!
Yay I'm so glad you liked it! :D
Thank you! The moment when it threw that error and everyone's heart sank 🤣. Great video and walk through
"Let me know if I'm repeating myself repeatedly" 👌🏼Love the humor! Keep up these videos, they are super useful and your delivery is great - clear and easy to connect with
😂 thank you so much!!! And will do ❤️
This was soo great. The unfiltered ones are the best. Could really see your thought process. Thank you! Subscribed definitely!
Yay I’m so glad to hear that!!
I've started learning from the SQL Sunday resources. Its very useful. Thanks for the content
Yay I’m so glad! ❤️❤️
This is amazing Tina, thank you so much!!! Please keep posting this amazing content!
For someone like me who wants to switch careers to data science, this is very helpful, please keep making videos. LoveYou :)
YAYY I’m so glad you enjoyed it!! ❤️❤️
I just completed my interview loop on Amazon. Thank you for the contents! All the best!
New subscriber and just wanted to say love your content, Tina!
Tina you are a breath of fresh air. Very precise presentation, perfect pronounciation in English, just the right length of the video. Keep the sql videos coming. Well done.
Great stuff, thanks 😊 I am working as lead analyst - digital transformation and wish to be a data scientist. Learning a lot from you, Thanks 😊
video was amazing as a sql beginner, it was so fun to actually understand the coding
1. start off with making assumptions
2. write steps to what you need to do (1.join 2.match by day …)
3. outline what a final output look like
4. code
5. review
6.optimize
Assumptions. Steps. Output. Review. Optimize.
Always Start Out Right, Okay?
Great video and I like this live coding style. If you continue live coding SQL/at all could you go through SQL beginner, intermediate, and advanced techniques? But, all that said, I really enjoy these videos.
I’m familiar with sql but this is very helpful I didn’t know you could query like this. Thank you!
excellent and on-point. Subscribed Tina!
SQL Sundays playlist made my day
Big thumbs up!! Very clearly explained. Really enjoyed it! It would be really appreciated if you could also make some videos to talk about what the real live SQL questions look like, because it seems to me that those real questions are quite different from Leetcode SQL questions (e.g. sometimes we need to come up with metrics to select ourselves, sometimes there is no sample result preview, etc )
Hi! Yup now that I’ve been working for a while, the actual hard part for tackling a SQL question is figuring out what the question even is + finding data. Also there’s the constant paranoia of whether your query is doing what you think it’s doing since there’s obviously no answer key D: I should definitely make a video on this topic! Thanks for suggesting!!
@@TinaHuang1 😄😄
Thank you, this is very useful and written effectively. Right pace and to the point.
this is good but i'll recommend covering basic data structure and leetcode type questions essential for data science interviews since not many youtubers have covered that and also since you have experienced the same in your interview :)
amazing video as always;liked ..
Yeah, someone else mentioned that too! Okay - will do some more research on what exactly companies are asking these days and draft for a future video. For my own data science interview, there were actually no algorithm questions asked. Although I was asked plenty of leetcode type questions for my SWE interviews so I do have some experience doing them.
@@TinaHuang1 oh is that so?no leetcode types(be it easy) questions asked for data science interview ? ok then you can continue making the videos in the same way then ! also it'll be really good video idea where you share your interview exp as a data scientist for the untitled company :)
oh and here is one reference interview exp with amazon for data scientist role
leetcode.com/discuss/interview-experience/324656/Amazon-or-Data-science-Intern-or-Seattle-Offer
Oh cool - will check it out! 😊
@@TinaHuang1 sure
ruclips.net/video/i2BjvSNJprA/видео.html
GREAT VIDEO! PLEASE MAKE MORE LIKE THIS!
Awesome stuff, liked the approach of writing given/asked
I have no idea what's going on and I want to start learning. This is giving me anxiety that I wouldn't be this brilliant
Liked the video, and subscribed to the channel. Liking the content, keep up the good work!
Yay I'm so glad! Thank you!
Thank you for this, I found this video helpful!
It's a nice video. I think your approach to the problem could be a little better. The goal should be to find three pieces of information. The count of those who only signed in from phone, those who only signed in from web, and those that did both.
The answer to the question is the count of those that do both. But the veracity of the answer is dependent upon all three.
An interviewer can be asking multilayered questions. You established your ability to write the query. You may have missed the opportunity to demonstrate your analytical skill.
Fair point! I could work on conveying more analytical reasoning! Thanks for the feedback :)
What kind of SQL would you reccomend learning? MYSQL or postgresql. Love your channel btw!
Thank you! ❤️❤️ it really doesn’t matter which kind, they don’t care as long as it’s not something wayyy obscure. I prefer Postgresql because it has more handy functions!
ruclips.net/video/i2BjvSNJprA/видео.html
Can you do a SQL learning series for beginners? it Will be great
Ohh I'll definitely consider it! Thanks for the suggestion 😊
Love the vid, thank you!!
Great video. Your solution was succinct and that's what interviewers want.
Wow! What an effective and thorough video... I will def be watching more. Which application are you using to store your code? I’ve attempted to keep a repository of useful code I always go back to but vscode is not good for that. The interface you’re using looks way better.
Better do aggregation before table joining, or simply use union instead of joining for this scenario. Log tables usually are huge, even after partitioned.
Aggregation before joining, I can understand..but why UNION? UNION is usually a "heavy" operation
Awesome! I recently bought a whiteboard to practice LeetCodes but I guess digital practice is even more important now. Also congrats on the rapid growth in the past days, I'm sure your channel will take off even more! 🚀
Yeah - very sad but impossible to avoid because COVID is ruining everything 😢 I think you mentioned that you just started university though so you'll still have plenty of chances to whiteboard for the coming years haha. And thank you!! You've been so amazingly supportive since day 1 on this channel and I'm so grateful 🥰
True!
Yes I just finished my first semester so I probably get to see both sides of the coin :D
But it's just so exciting I can't wait to start haha!
I'm glad you appreciate it!! Your videos have been really helpful and I wanted to give back a bit of that positive and helpful energy! Keep going! 😁
Will do!! ❤️❤️
ruclips.net/video/i2BjvSNJprA/видео.html
very impressive. pace is perfect. Just keep the same way nd make more videos.
Should we not pre-aggregate the tables to day frequency before joining? The result would be the same but would it not be more optimized since the number of rows will decrease for both tables before joining?
Hey this was pretty simple for me and I am not a data scientist nor an engineer. I used codecademy course on sql, learned it in about a week. I highly recommend it.
Yup - the actual questions themselves are really not very hard at all :) what’s the most important for these interviews is your ability to communicate and think critically/course correct when challenged
Thank you, so much for such detailed videos, this is super helpful.
Hello I Tina. I liked and subscribed. Do you have an option for Date aswell ? Jk. Keep the content coming. If you publish any short courses I would totally buy that. Great video clear explaining 👍
Nice! Keep it up! It was very useful. Please do more!
I have!! haha there are 7 more as part of SQL sundays playlist linked in descriptions :)
I was thinking about getting a whiteboard for quite some time and now after watching this i'm 100% getting one
Hi, Tina...videos are very nice and intuitive. Keep adding more videos on SQL and please gives links for .SQL file ..for our practice
I totally enjoyed the video. Keep it up!
Thank you!! This was great!
Finally I understand everything in the video 😁😁. Great video, thanks.
YAY
Nice stuff for beginners like me. But, when do you stop learning or giving time to a language? when do you say it's more than enough?
This is super helpful , gonna binge your other videos. Thankyou.
Yay in so glad! 😊
I am so glad I found your channel! My goal is to learn SQL (I only know R so far-not enough!!) and this content is super helpful. :)
How long does it take to learn R?
Great explanation, but I believe the query is false, you need to specify a "full join" instead of just "join"
Because in your query if one user exists in just one table (web or iPhone) it won't be selected (inner join)
Cheers
I am completely shocked, that no one is pointing that out...
But the question says 'from both iphone and web' ............. Am i correct????
I like the content..... Thanks for taking time to create such great content.... and may the channel hit 10,000k subs soon ☺️
Yay I'm so happy you like it ^^ Fingers crossed 😊
Thanks Tina for providing great content
i guess you can also try union instead of a join as both tables are of a similar structure. I don't know about Postgres, but MySQL does have that.
UNION is the least optimal solution...avoid it unless you need it
Same here, instead of matching to multiple keys in the join where ts is probably non-indexed, you can directly aggregate twice after a union to arrive at the same answer
Nice explanation, Tina!
Thank you!
Thanks a lot. Now i can know about interview questions
Thanks for making such a helpful video! I was wondering where can get I these questions from??
Hi! I got these questions from Glassdoor but in my other SQL Sunday videos i get them from scratascratch, which I have an affiliate link for in my other SQL Sunday videos (at no cost to you) if you wanna check that out :)
I'm confused. Knowing sql will make me a data 'scientist'? 😱 That's a dope job description.
two things:
1. would date diffing on i.ts and w.ts less than 24 to catch global clocks have been bonus points or outside of the question?
2. is GROUP BY 1 short hand?
1. outside of the question, since the time would be server time which sould just be in one timezone
2. Group by 1 is group by first column, so yes shorthand
She could have also used USING(id) instead of ON...
thanks for the video . great!
It is really helpful. Thank you!
In my view it will be more meaningfull to create a session table rather than iPhone and web. The session table should contain a column to represent the client type.
Love the content!
Great job good speed and most of use probably watch on 1.25 seep
Great walk through!
Thank you!!
ruclips.net/video/i2BjvSNJprA/видео.html
Hello Tina, thanks for the video it is really useful. in this questions you used pg instead of mysql and i wonder during the interview, did you got to choose which you want to use?
Hi! Yup I prefer Postgres because it has way more handy functions. Companies don’t care which sql you use usually unless it’s some really weird and rare one haha
Do you have any resources for SQL questions sets that you used while preparing? Thanks again for the content Tina!
hi! thanks for making this video -- it's super helpful. could you explain why you grouped by 1?
Group by 1 means grouping by 1st column from your select clause ... u can use group by 2 also to group by 2nd column
Tina , I think you can have another column with date data type and use that date data type column in select where and group by because if we think that table has millions or billions of records i think date_trunc will affect performance negatively, please let me know your thoughts, thanks.
👏🏼👏🏼👏🏼👏🏼 thanks for ur knowledge sharing... liked it 😅
very effective tutorial, thank you very much for your effort!
What do you do your GROUP BY with 1 and not with the non aggregate columns of the Select statement, as the aggregate functions syntax requires it? I do not understand.
I bet you heard this a lot but you are a very good teacher!
ahh really?? I'm so flattered! Nobody has told me that before haha. Thank you!
ruclips.net/video/i2BjvSNJprA/видео.html
Can we also use an intersect instead ?
with used_both as
(
Select distinct date, user from iphone
intersect
Select distinct date, user from web
)
select u.date, count(u.user) from used_both u group by u.date order by u.date asc
I always encourage people to try it out 😉 it’s the best way to learn! Also tbh it’s been a while since I’ve done this question and don’t exactly remember 🙈
good job, but this query will not scale with number of sessions. E.g. imagine edge case when one day has 1 billion sessions from 100 users. It will be much more optimal to select unique users from each table, then join, then count.
Very true - pointing that out in interview would be awesome!!
Thank you for this video, advantageous one !!
Good video! PS: would appreciate if you explained Group By 1 in the video. I had to stuck overflow it ;)
Ohh true! Will do next time :) Thanks for letting me know!
Hi Tina, thanks for sharing this. I have a small question (might be stupid): if some users log in several times both from iPhone or web, I mean 'multi to multi relationships', could we still use these queries? Or should we make any changes? Thanks so much!
Hello stella, I just tried this case out (same user logs in multiple times on both iPhone and web in one day) and the user is still only counted once. I believe this is because of the DISTINCT statement. On a given day each user can only be counted once.
Tina, this is awesome. Really like your videos here. I am exactly in line with you, I started my channel to try to help people in the career of data as well. Cheers.
Thank you 😊 And that's awesome!!
ruclips.net/video/i2BjvSNJprA/видео.html
Great video. Very useful indeed. THANK YOU SO MUCH
Hi Tina ! Thanks for your content . I have a solution with WHERE instead of JOIN to join the tables. In your opinion which one is the best in term of optimization? and why ? thks:)
It's hard to say. It's really depending on the database you are using, the amount of data you are running your sql against and the tables structures, but usually the JOIN is faster, because the databases have several algorithms for performing the joins (hash join for example is faster when you are dealing with small tables, while merge join is faster when you are dealing with large tables), while the where clause depends on the structure of the table, like does have indexes, are the statistics valid, is the index created on the column in where clause...and the optimizer may or may not chose to use that information in the where clause and just do a full table scan, which is something you tend to avoid...
Thanks for this video please do more on the subject
Great video! Thanks for sharing 😃👏🏻
This will be helpful for you.
You should check out azure data studio is a free Microsoft sql ide.
But it only support sql server and postgress. Also
Support notebooks with python or sql
Kernel.
Ohh will do! Thanks for suggesting
Can you please explain Why have you used join on date column here ,Is it needed? and how results would have been different if you skip date condition ?
Which SQL is better to lear MYSQL or Postgrace SQl to stay in Data Domain
Hi Tina to practice these live code interviews do you have any question banks or resources you would recommend, besides LC or hackerrank?
My only question is the assumption about the inner join of the iPhone and web logs. Wouldn't it be more logical if the entity were concerned with customer activity from any source and the "test" for the interviewee would be to consolidate customer activity from any source and then count for "uniqueness"?