Solving VERY HARD SQL Interview Questions on Analyst Builder

Questions Page on Analyst Builder: www.analystbuilder.com/questions
Complex Address Question: www.analystbuilder.com/questi...
Let's look at how we can solve a very hard SQL technical interview question. In this series we work our way through easy, medium, hard, and very hard SQL interview questions that you may see in a technical interview.
____________________________________________
RESOURCES:
💻Analyst Builder - www.analystbuilder.com/
📖Take my Full MySQL Course Here: bit.ly/3tqOipr
📖Take my Full Python Course Here: bit.ly/48O581R
📖Practice Technical Interview Questions: bit.ly/46pDqqL
Coursera Courses:
Google Data Analyst Certification: coursera.pxf.io/5bBd62
Data Analysis with Python - coursera.pxf.io/BXY3Wy
IBM Data Analysis Specialization - coursera.pxf.io/AoYOdR
Tableau Data Visualization - coursera.pxf.io/MXYqaN
Udemy Courses:
Python for Data Science - bit.ly/3Z4A5K6
Statistics for Data Science - bit.ly/37jqDbq
SQL for Data Analysts (SSMS) - bit.ly/3fkqEij
Tableau A-Z - bit.ly/385lYvN
Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!
____________________________________________
BECOME A MEMBER -
Want to support the channel? Consider becoming a member! I do Monthly Livestreams and you get some awesome Emoji's to use in chat and comments!
/ @alextheanalyst
____________________________________________
Websites:
💻Website: AlexTheAnalyst.com
💾GitHub: github.com/AlexTheAnalyst
📱Instagram: @Alex_The_Analyst
____________________________________________
All opinions or statements in this video are my own and do not reflect the opinion of the company I work for or have ever worked for

[@manthanmistry1205]

Now that's a HARD one Alex. Great content ❤

[@bluedeveloper6969]

Waiting for the very very hard questions XD. Loving the videos keep it up!

[@reliablevariable1615]

My immediate concern was the leading spaces when you wrote the query! So glad you addressed it. Once I write a query that accounts for a leading or trailing space, the immediate question I would ask if I need to account for it in the output. If the answer is yes, I address it immediately once I have figured out the query so I don't forget. If you really want it tricky, you'd throw in an address or two with dashes in the city or address. But that's be a very very hard video as opposed to a very hard video. Throw in foreign addresses and you'd get darn near impossible lol

[@gamingboyz8407]

@Alex Analyst thanks for this wonderful information ❤

[@eren_1oglu]

I started to learn SQL earlier but had to pause for a while because of school... Now I'm starting again. Thanks for these tutorials and real-life examples

[@rizkilaksana2830]

very cool alex

[@rarepepecombos8743]

Alex can you please answer my question, I am an undergrad with experience in a bit of basic statistics and sql. I want to join WGU MSDA (Masters in Data Analytics). Do I need more difficult math skills beyond basic statistics (Chi square test, standard deviation).

[@Flintbox]

Wonder if you wrapped it in a trim function would it also show as a correct answer?

[@AlexTheAnalyst]

Depends on how you write it, but that could be helpful!

[@pipertripp]

Why not use regular expressions on at least some of these fields? My biggest concern with regex would probably be performance, but would allow for very flexible parsing.

[@VusaMate2]

Hey Alex maybe as a feature we could see other user’s submissions so that we can see what other people have wrote

[@AlexTheAnalyst]

Yes! We are working on this feature :)

[@user-pk9qn6om5s]

By doing your SQL course will I be able to answer the very hard questions?

[@AlexTheAnalyst]

Try it and see!

[@arpan8076]

Where is the script man ?

[@Jackjunk33]

I don't have paid version of analyst builder so I can't check for sure but I worked on getting an MSSQL query to work and here's what I got. Super sloppy and I'm sure it could be done better but it seems to work! CREATE TABLE #ADDRESSES (address NVARCHAR(250) ); INSERT INTO #ADDRESSES (address) VALUES ('123 Main St Suite 5A-New York-NY 12345') ,('456 Park Ave-Minneapolis-MN 34563') ,('789 Elm St-Goldsboro-NC 23578') ,('1010 Broadway-Maples-MA 32167') ,('1111 West St Unit B-Flowertown-FL 94566') SELECT CASE WHEN [address] like '% Suite%'THEN SUBSTRING([address],1,CHARINDEX(' Suite',[address])-1) WHEN [address] like '% Unit%'THEN SUBSTRING([address],1,CHARINDEX(' Unit',[address])-1) ELSE SUBSTRING([address],1,CHARINDEX('-',[address])-1) END AS Street ,Substring(substring([address], charindex('-', [address], 2) + 1, len([address])) -- Starting Point ,0,CHARINDEX('-',(substring([address], charindex('-', [address], 2) + 1, len([address]))))) AS City ,Substring(substring([address], charindex('-', [address], 2) + 1, len([address])) -- Starting Point ,(CHARINDEX('-',(substring([address], charindex('-', [address], 2) +1, len([address])))) +1),3) AS State ,RIGHT([address], 5) AS postal_code --SUBSTRING('New York-NY 12345', CHARINDEX('-','New York-NY 12345') +1 ,3) AS ExtractString; --,SUBSTRING([address],1,CHARINDEX('-',[address])-1) as Address1 --,substring([address], -- charindex('-', [address], 2) + 1, -- len([address])) AS StartingPoint FROM #ADDRESSES DROP TABLE #ADDRESSES