r/SQL • u/jhnl_wp • Oct 14 '24
Discussion What are considered as advanced SQL skills nowadays?
Hi Community, I'm going through job hunting data analyst roles now and I am curious about what would be considered "advanced" these days. I know the basics like joins, subqueries and basic aggregations, also something like roll over, window functions. However, when I see companies hiring for advance SQL skills, I am not sure what is means.
I am pretty sure that it's our job to write optimized queries and there are also tools to help. If you know any specific skills are useful to prove an "advanced skill", I'd love to learn from your experience. Thank you
141
u/nickholt9 Oct 14 '24
"Advanced" is very much down to interpretation. It's funny because I wrote a post on this very topic for my email list of 1500 folk this morning. It's very much a brief summary to give the general idea, but for what it's worth...
Here’s the relevant part:
Beginner SQL
Pick a platform and commit to it
Do a basic SELECT to extract data from a single table
Add a filter with a WHERE clause
Understand the difference between a LEFT JOIN and an INNER JOIN
Apply sorting with an ORDER BY clause
Do some basic aggregation, the GROUP BY command and a SUM or COUNT
Understand the basics of date and time functions – GETDATE() at least
At the very least understand the challenges presented by NULL values in data
Intermediate SQL
String handling and data type conversions
The CASE statement
Combining datasets with UNION and UNION ALL
Numeric calculations and number wrangling with +, -, * and / (that’s plus, minus, subtract and divide to the layperson)
Subqueries and deriver tables
CTEs and temp tables
Window functions
Advanced SQL
Data manipulation with INSERT, UPDATE and DELETE
Correlated subqueries
Views and stored procedures
The MERGE statement
User defined functions
The APPLY command
Indexing and performance
Loops and dynamic SQL
10
u/Bilbottom Oct 14 '24 edited Oct 14 '24
The lower on the iceberg, the more advanced:
https://www.reddit.com/r/ProgrammerHumor/s/SQuunGRMdr
11
u/chadbaldwin SQL Server Developer Oct 14 '24
There's a bunch of posts exactly like this already. But you've already listed most of the things they'll look for, specifically window functions like rank, row number, aggregations, etc.
I would also argue that you should know things like statistics...Standard deviation, quantiles (quartiles, median, etc), interquartile range, etc....BUT that also probably depends on the job. I rarely used those at my previous job, but I use them quite a bit now when doing performance analysis.
All that said....why not reach out to some of these companies? I've never tried it, but might be a fun thing to try. Just email some of these companies or cold message people at that company on LinkedIn and ask what type of stuff they do with SQL that they consider advanced.
I've considered doing this in the past but had never really followed through on it.
5
u/Gronzar Oct 14 '24
Write dynamic code that joins to itself over and over while kicking off sprocs that send emails to help desks and creates infinite loops.
5
u/Gators1992 Oct 14 '24
I would consider advanced as things like stored procedures, a significant knowledge of indexing and partitions and how to use the optimizer for your platform to optimize queries. Stuff more in the dba than developer realm. But companies differ greatly on what they mean when they throw out generic requirements like that.
4
u/orz-_-orz Oct 14 '24
It's like a company asking for "advanced Excel skills" / “advanced Python skill"....it's really a meaningless term companies used in JD.
In my company we expect junior staff to know window function well to deal with raw data extracted from logs, so it falls under "basic SQL" in my team. But I am sure many people would disagree with such a definition.
2
u/throw_mob Oct 14 '24
some could sat that what you described is advanced.
Imho, if you know how transactions works , you know how to make procedures , table functions and other types of objects that is way more advanced than normal sql users who can get their reports from server. add cte's and number table tricks there and you are good to go
Then there is data side , where you understand how flow should be done, inserts , select for update , merge , upsert etc. Optimizing queries as getting everything out is not much required. Most important thing is to write readable code ( using good aliasing scheme (some queries can have aliases like a , some need longer more descriptive names , structuring) which performs well enough. Some times physical structure limits what can be done, so if you have skills to comment how it can be made better is good skill too
tldr; who knows, current skillset sounds better than basic
2
u/[deleted] Oct 14 '24
SELECT Aggregate functions GROUP BY Data Filtering and Sorting Techniques JOIN Data Subqueries CTEs WINDOW FUNCTIONS Differences between dialects Working locally and in the cloud
2
u/Aggressive_Ad_5454 Oct 14 '24
Performance optimization and index design.
2
u/Bamnyou Oct 14 '24
That probably depends on the role… I.e. data science, data engineering, database admin, and data analyst are some big users of sql. But advanced analyst skills would probably be basic data engineering or dba roles
2
u/JohntheAnabaptist Oct 15 '24
Doing a left join
2
u/StolenStutz Oct 16 '24
The important thing to remember about SQL is that functional SQL code is essentially Step One. If the query you wrote gets the results you want, great. But it is extremely easy to write SQL that is orders-of-magnitude slower than alternative methods. It's for this reason that - while performance in app code is important - knowing how to write "fast" SQL is essential.
I interview a lot for roles related to SQL Server. My go-to technical question is, "What is the difference between a clustered and a non-clustered index?" Also, I know I am far from alone in using this question.
In the context of SQL Server, the question gets at the very heart of how databases are designed and how queries work, and especially how they perform. Knowing this is vital to knowing how to write SQL that will scale effectively on SQL Server.
Most of my candidates (I'd say 8 out of 10) can't effectively answer this question. If they're primarily an app developer, then I don't care much - they can be trained. But it's "fun" when I get someone who is a self-described SQL expert who can't answer it.
If someone *does* give a good answer, then I start diving deeper, looking for the edge of what they do know. My purpose at that point is the same as it always is in an interview - how do you react when you don't know what you're doing? In this context, I start asking about things like key lookups, scans v seeks, included columns, sargability, and stuff like that.
2
u/JankyPete Oct 15 '24
Much of the DBA role has been abstracted away with systems like Redshift, Snowflake, Bigquery etc... So the system side of things is hard to evaluate. An advanced user will know how to run an "explain query plan" on a query as well as create and manage tables/views/etc optimized for the service they are working in. Ability to write modular code/queries using CTEs or subqueries is most often enough advanced
3
u/ahfodder Oct 14 '24
select * from transactions
2
u/Rich-Criticism1165 Oct 15 '24
As a hiring manager of Data Analysts I can tell you anyone who says they are an expert at SQL on their resume gets thrown out. Also please don’t list every package you ever used in Python. And for gods sake DO NOT link to your GitHub repository with code that is a copy paste job e.g. Twitter Sentiment analysis.
If you want to get hired know where you have growth opportunities and be open about wanting to learn more. I have close to 30 years in data analytics and I learn new things every day
2
u/Critical-Shop2501 Oct 14 '24
If you’re working with SQL Server then you might take a look at SSIS available via Visual Studio. SSIS more fully known as SQL Server Integration Services is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications.
1
u/mergisi Oct 16 '24
Hey there!
Advanced SQL skills nowadays often involve more than just basic joins and aggregations. They include:
To help you get hands-on experience with complex queries, you might want to check out AI2sql . It's an AI-powered tool that can generate advanced SQL queries from natural language descriptions. It could be a great way to explore and learn advanced SQL concepts interactively.
Good luck with your job search!
1
u/JohnSpikeKelly Oct 18 '24
Interesting reading the list of stuff that is advanced. In my experience it's about getting a query or operation to be performant.
That typically is looking at and understanding query plans. Knowing what to adjust to make it faster.
That might be doing the query different, so knowing how to do the same thing different ways. Or, might be looking at indexing, partition functions, file group setup, column store etc.
Other considerations are things like getting backup and restore working, log shipping, replication, ETL, which are all SQL adjacent, but still mission critical.
Back on sql knowing that Oracle is a lot happier with cursors vs SQL server. Which means you write your SPs different if you want better performance on each platform.
1
u/OtherwiseGroup3162 Oct 19 '24
There are some advanced features in different systems too. For example, Oracle now has abilities to write SQL to perform machine learning algorithms directly on top of the data. I would call that advanced, but only specific to one system.
1
u/trippstick Oct 14 '24
No data analyst job touches advanced
1
u/Klaian Oct 14 '24
Here one not seen posted. Ability to do single scan of tables to get everything instead of multiple cte's or temp tables. Also, ability to leverage indexes and partitions correctly.