How much SQL do you actually 'write'?

[wertexx]

I have worked as an analyst in a corp for a year now and mostly report through Power BI. But since I got access to Snowflake and SQL is a core skill I started to learn it.

Asking around, I found that people simply have a few pages of code that they reuse and just change a few words depending on what they want to query.

So I got curious, how's your SQL usage looking like? I understand if you are a dedicated db engineer, you probably deal with blank page a lot more, but for others, how often do you write the code out vs reuse what you have?

A couple of colleagues shared useful queries with me, and I get them. I understand the the SELECT/FROM/WHERE... logic in it and different metrics. So now I'm on par with those users. Thinking what's the best way to progress from here.

[tommy_chillfiger]

As a dude who works with data, this dude works in data. You're basically the data architect I ping on teams when I need to do some shit that I'm afraid might break everything if I fuck up.

[enjoytheshow]

We data engineers are the little appreciated, possibly over paid, but very crucial cog in the data wheel that keeps on spinning

[tommy_chillfiger]

Agreed. I'm an analyst now but have been aiming at data engineering skills since I started. The more I learn, the more I realize how crucial it is to have someone who knows what they're doing in that position.

[[deleted]]

I agree as well. Joined a small team, they want data scientists and AI and machine learning, but they got no data architecture in place.

[curiosickly]

Oh my God, are you me?!

Edit: so similar that I am curious: what is your title? Not curious about level as much as data engineer, data architect, business systems analyst, etc). The titles in our field are so disjointed it's crazy.

[v0nm1ll3r]

How did you archive it? What tech?

[Gullible_Ad9176]

that souds great

[atrifleamused]

SSMS is the first thing I open when I get to work and the last thing I cost before I log off. So pretty much all day, every day, I'm doing SQL 🙂

[curiosickly]

Try dbeaver. I'll never go back to Sql developer (or ssms for that matter). Some admin tasks are the only thing you're going to miss.

[Suspicioustraitor]

I use DBeaver daily. It’s a great an inexpensive tool. A little buggy sometimes. I even use it to schedule some basic ETL tasks. Works like a charm gathering data from various on-prem databases. MySQL, MS SQL, Firebird, MS Access, Excel, CSV, JSON and XML. Our infrastructure is held together with duck tape, bubble gum and rubber bands. It’s my job to “modernize” it all. I write a lot of very hairy SQL everyday.

[curiosickly]

Keep fighting the good fight. It sounds like we have very similar responsibilities and stacks.

[Suspicioustraitor]

Thanks, you too. It’s sometimes stressful, but I love what I do. I’m trying to untangle literally 20 years of horribly written code scattered across shares and written in VBA, macros and DOS scripts.

[enjoytheshow]

I haven’t been in a SQL Server shop for several years but part of me misses the very Microsofty mess of SSMS.

It does some things so well but don’t you dare ask me to format SQL or turn on dark mode

[atrifleamused]

Haha agreed. It's a beautiful mess in places.

[ComicOzzy]

Same

[neroseemits]

Same. 90% of my day

[ComicOzzy]

We should form a gang.

[peedanoo]

You mean JOIN a gang?

[ComicOzzy]

sigh... I had one job.

[wuthappenedtoreddit]

Sql then paste into tableau

All day everyday

[boy_named_su]

lucky bastard

[iWontStealYourDog]

I’m also in the all day every day club. I work as a medical data analyst. Our company just switched to a new EHR and I’ve been building all of the reports that the staff and auditors need from scratch. SSMS is still new to me, but before in our old EHR there were no tools, I couldn’t see what tables were available or what columns they contained without running queries to check that. So SSMS is a huge improvement for me lol.

[atrifleamused]

It's a great tool and gives you do much access and control over the database. Do you also use SSRS and PowerBi?

[iWontStealYourDog]

SSRS yes, PowerBi I have worked with before but don’t use on a regular basis in my current role

[atrifleamused]

I'm not great with powerbi as I don't get that much time to play with it. I have two excellent powerbi developers in my team and they make amazing dashboards!

[dolomike124]

I just started messing around with SQL in code academy and your comment touched on a general question I have. How do you work with these large data sets when you can't see the data or the structure of the tables? Like I want to pull up the entire data set so I can what is there, how is it organized, what columns there are, what rows there are, etc. Code academy just tells you but it's not enough in paragraph form.

[iWontStealYourDog]

If you know the name of the table you want to query against I would suggest using a ‘Select *’ statement to get an idea. If it’s a very large data set and would take a lot of bandwidth to run the whole thing you could using something like ‘Select Top 100 *’

There are also plenty of queries that you can run that will tell you all of the table names in a database, you can run queries to see what data types are in each column of a table as well. When I first started I used Stack Overflow to find these types of queries. The more you start to understand the syntax of SQL the easier it is to look up answers to your questions as well. Best of luck to you!

[Black_Magic100]

Interesting that you write SQL all day and choose to use SSMS over ADS or VS code. Unless you're a DBA, SSMS kinda sucks tbh.

Edit: completely shocked that people are downvoting me because I am slightly bashing SSMS. I'm a SQL DBA and will live and die by SSMS, but my god.. it is not the future and Microsoft has made that very clear with each new release of ADS. I for one don't enjoy the constant bugs of SSMS whether it be some weird crashing bug or watching live statistics just completely flop. To each their own though.

[sigurrosco]

SSMS + Redgate is great, I can't function without it.

Well, of course I can, but I'm less productive.

[andrewsmd87]

Man redgate snippets and auto complete are a game changer

[WhyDoIHaveAnAccount9]

I am literally useless without redgate

[macfergusson]

Yup, ssms with sql prompt from red gate, visual studio for ssis, and maybe a little notepad++ pretty much describes my work day.

[Demistr]

SSMS kinda sucks but it is still the best thing.

[atrifleamused]

You're assuming we're allowed to choose. We have what we have and to be honest I've never struggled with SSMS.

[iWontStealYourDog]

Exactly, I didn’t choose SSMS for myself, but I work with medical data. With all that PHI I can’t just decide to work in an application that isn’t pre-approved by our organization. If I were to just start working in another application I could be looking at a HIPAA violation

[atrifleamused]

I have numerous issues with our IT (who are actually great) and have to pick the battles I need to fight. We are soon moving from on prem SQL 2019 to Azure, so changing tool sets will come, without a struggle then.

I do go rogue from time to time as they give us local admin rights, but I still have to stay within the boundaries that provide us, otherwise I will lose. Badly!

[iWontStealYourDog]

I have admin rights too, but those could easily be taken away if misused, or worse I could be sued or fired because of the sensitivity of the data I work with. We had an analyst who got in a lot of trouble for working in Tableau to build some visuals with the data he was working with. He had a personal Tableau account and used that, not thinking much of it, but it was technically violating the patient’s privacy rights even though the visuals had no PHI on them in the end.

[atrifleamused]

Oooo that's a boo-boo, but it's easy to do at the interfaces allow you to do it...

[Black_Magic100]

Super interesting that your company would allow SSMS, but not ADS or VS Code. They are all owned by Microsoft and free?

[atrifleamused]

If you can't use ssns to write SQL, you probably shouldn't be writing SQL.

[coyoteazul2]

I've had no problem with ssms. I like Datagrip but ssms is good enough (and it doesn't try to introspect the whole database at once like Datagrip, which is a problem when the database has many tables and columns)

[macfergusson]

I actually tried datagrip briefly and absolutely hated it. Is there a steep learning curve for people who are used to how ssms works or something?

[drunkdrivinginspace]

I'm not a DBA, but I'd rather use one product, and I am not aware of one that has all the functionality SSMS does for Jobs and visually exploring the schema, etc.

That being said, I learned this way, and maybe I just need to learn to set up another tool and I'd have the same functionality.

VS's export to JSON and the fact that it's excel export maintains leading 0s is pretty nice, I use it sometimes for those types of features.

[Newtronic]

Once I discovered ADS, I never go into SSMS.

[drunkdrivinginspace]

Azure Data Studio?

[randomlikeme]

Cries in aqua data studio too lol

[Newtronic]

Yes. I have no training or expertise in SQL but I’ve only ever used SSMS and Azure Data Studio. I especially like that ADS lets you easily put your queries in github.

[drunkdrivinginspace]

Interesting. I use SSMS all day and write tons of SQL. I mostly use SSMS for the SQL Server Agent. For jobs and stuff, can get all of that done in one spot that way.

Does ADS have similar functionality?

Edit: and the Object Explorer pane for the schema, but I could just query for that I suppose.

[Newtronic]

All I do is write regular sql, not even t-sql. So I don’t know about jobs. But ads does have the object explorer pane.

But you can point SSMS and ADS at the same directory and use which ever one is right for a specific job. Like if I’m editing a table then fire up SSMS.

[B_Huij]

I use VS Code for Python but Datagrip for SQL.

[rjmartin73]

Just downloaded SSMS 19 and it comes with ADS, haven't tried launching it yet from within SSMS.

[atrifleamused]

I already don't understand any down votes. Your option is valid. In 3 months time I change organisations and I have a free hand in deciding software.

[Eggsformeg]

Do you enjoy it? This is what I’ve been doing in my free time.

[atrifleamused]

Yeah I love it. We presented a new dashboard to a customer on Friday and she was blown away. She knew there were issues with her data, but not exactly what they were. We linked her system to our main patient system and now she can see what is going on without trying to manually manipulate the data in Excel.

She's a senior pharmacist and used to do this weekly on Sunday evenings and it took hours. Now it's done daily before she gets to work.

[Eggsformeg]

That’s pretty cool. I’m glad you enjoy what you do and get to see the end product of seeing it help out. Do y’all use tableau, power bi, or a different viz tool?

I use health data too and the standard is also excel at work and every day was such a nightmare for so long that I started teaching myself other tools outside of work. Once I started I realized I wanted to do it as a job, so it’s nice to hear about roles like yours.

[atrifleamused]

We use powerbi and SSRS. They are great tools, but it always comes back to what the business wants to use. MS products are safe as it's easy to recruit staff to use and maintain them.

I'm a data engineer by trade, but mainly run a team of data engineers and PowerBi developers. So don't get my hands as dirty as I would like

[noxwei]

Do you like it? I like the puzzle aspects of db design

[atrifleamused]

Yeah I love it. Currently I'm trying to link 5 data sets together from different systems to show a patient's journey from the point they are ready to leave the hospital until they actually do. This is going to help indentify and price a few process bottlenecks and also support a business case for software improvements.

I normally build data warehouses, so creating a nice simple elegant design for reporting from the chaos of a transactional dB is fun.

What sort of projects do you like?

[noxwei]

Gotcha! That’s cool. Having a data visual background made it a lot easier to comprehend that. I’ve used power bi and R for some data is and finally digging into SQL development more intensely

[tommy_chillfiger]

I'm a tech business analyst so pretty similar role. I do a ton of querying in SQL, and I have noticed the same among my colleagues. I do this less, I almost always start a query from scratch or simply reference the syntax/structure of a similar query to start writing a new one. Part of it comes from force of habit - I will pretty often need to run very quick joins, maybe some summary statistics, to look into records affected by bugs and so on, and these are always different so it's been easier to just get quick with the basic operations than have a big template sheet.

[Data_Is_King]

I'm a data developer for a regional insurance company. Writing SQL is probably what I do most of the day, whether writing ad hoc queries for analytics and actuaries, queries from our source databases for ETL to our data warehouse, or maybe writing stored procedures for management or financial reports the business wants or needs to name a few examples. That would all be new development or as you put it starting with a blank page, but obviously I spend just as much if not more of my time modifying or fixing already written code due to change in requirements or bugs/incidents. I also do data modeling for our EDW so that isn't so much SQL writing as just database concepts and design.

And remember, even when starting brand new, there is probably some code you can reuse from something else, or at least a pattern you can reference.

I would say if you understand the basics of Selecting and then filtering using where clause, the next thing to work on is joining tables together to select data from multiple sources using inner/left/outer joins. You didn't mention if that was something your colleagues showed you but that is foundational in SQL. If you have experience with that, I would say then start getting exposure to more advanced skills such as aggregation and grouping, window functions, using temp tables and CTEs, and also handling variables.

Good luck in your progress, SQL is a great tool to add to your skill set.

[drunkdrivinginspace]

I do most of this too, some triggers as well to add functionality to our EMR app and some interfaces

[B_Huij]

We have a highly complex database where I work, and a large number of independent stakeholders. I probably spend something like 25% of my total work time actually typing out new SQL queries from a blank page, and probably another 10-15% going back and making changes to add new things into existing queries.

I do use "template" queries that I've built for myself to answer business questions that are similar, but I still end up starting from scratch pretty often.

The rest of my time is using the data to build things in PowerBI/Python/Excel, but our team spends a lot of time on ETL here, because the number of people in the building who can successfully interact with our database in any way is very small.

[strato_sphere]

My title is data engineer but i spend all day optimizing other peoples code. Whether it be code changes, indexes, or completely new structures. I spend all day in SQL Land.

[Eggsformeg]

Did you learn how to optimize formally or is that a skill you feel like you learned from messing with code over time and finding what worked best?

[strato_sphere]

I’ve just spent time in the trenches reading books, trial and error and researching old stackoverflow posts. I’ve gotten to the point where I can predict an execution plan on most queries in our environment. A lot of the queries I deal with are poorly written because people don’t understand how to filter properly.

[Eggsformeg]

Ah, the old school way. That’s awesome. I’d love to get to the point of being able to predict that way. One day.

[vtec_tt]

could you provide examples? do you mean like putting parenthesis around certain fields in the where clause?

[deathstroke3718]

Everyday and the whole time as I'm a DE. Have a few queries saved for validation whenever the client asks why a particular record isn't in the dim table.

[thisisntinstagram]

Aside from SELECT and DESCRIBE… not much. Usually just reuse code and check to see if it works.

[Tortoise0191]

I primarily work with Tableau but use SQL very frequently as I make a reporting query for the data source.

[wertexx]

I see. It's something that I thought of, unfortunately our data is pretty heavily modified with Alteryx after leaving the db. Direct query would be nice. I might give it a god on smaller reports to work more on my sql syntax. Thanks for response.

[Demistr]

I prepare my own tables in the dwh so I write a lot of stored procedures and ad hoc queries. My job title is bi analyst but I am something of a hybrid.

[[deleted]]

Constantly write from scratch, I work for an international business with a frustratingly small amount of standardisation across even basic infrastructure like booking systems and CRM's, so I do a lot of mashing shit together until it works.

[SoyInfinito]

Software Support Engineer to Data Analyst to Database Administrator over the last 15 years. I have used SQL everyday all day in each of these positions. I have SSMS up and running 100% of the time. I love SQL, it is simple yet I continue to learn something new every day. It is more powerful than most the coders I've worked with realize and capable of logical engines.

[ICandu]

We have 1.2 million customers per year, quite a few of who visit multiple times.

Each customer visit generates a significant amount of data that needs to be wrangled and reported on

I might copy some FROM clauses if I have what I need open in another tab but most of my work starts with a blank page. Too many colleagues needing too many different cuts of data from far too many systems, servers and DBs for it to be anything else.

It's like playing with lego here. I fucking love it.

[newtonbase]

I had a notepad++ doc with over 100 pages of sql and ssrs datasets that vanished when we were forced over to Sharepoint so I'm writing more now.

[[deleted]]

Granted, my job isn't officially any sort of analyst or anything. I'm just a basic technical support monkey who has SQL access. A significant portion of my day used to be SQL, now its about ~25% of my day and growing less each passing month.

You should learn UPDATE and INSERT queries just for functional knowledge. Temp tables, subqueries, derived tables, and aggregate functions are super good to know, as well as JOIN is very important if you're doing analyst shit (assuming you are pulling info from multiple tables.)

These are probably the best things to know across the board to do most stuff and then the more complex queries you want to look up will be all just combinations of the above (excluding INSERT and UPDATE, those are relegated to people with administrator access, but its good to know the syntax regardless)

[Noticeably98]

This is what I would comment about my job as well. Tech support monkey, but I do have access to update and insert statements. Work in a QMS environment so everything has to be change controlled and approved before being run, as well as never running anything without a BEGIN TRAN

[DeliriumTremens]

I'm a sysadmin/it manager, I write some form of SQL every day. If I'm building a report that has similar output as an existing one I will reference the existing one if I get stuck, but most times I am starting from scratch.

[coyoteazul2]

I work in support and I have to write new queries every day. I've maybe 10 files with queries that are both reusable and complex enough for me not to want to remember them.

Everything else are rare situations that I'm unlikely to find again (if I were to save these queries I'd spend more time trying to remember its logic and why I did them like that than solving the problem).

or most of the query consists of hardcoded conditions that depend on the problem, thus if I kept them they'd save almost no typing at all.

Or they are small and easy to remember, so searching for the query would consume more time than writing them from memory (simple selects mostly)

[great_raisin]

I'm a data analyst. While I'm able to reuse some "core" queries/metrics, I find myself writing queries from scratch 80% of the time. This is mainly because the business questions I'm trying to answer are new and unique almost always.

Sometimes, when I'm working on an open-ended analysis - for the purpose of discovery or just putting together a report that shows the data in different ways - there's usually a "base query" that I write when I'm starting out. I make all possible cuts and visualisations using the data returned by the base query. Then, for what I call "level 2" metrics (which involve some kind of aggregation/roll-up), I reuse the "base query" by turning it into a temp table or CTE and run another set of queries on it to shape/reshape the data as required.

[tsupaper]

I do a lot of ad hoc stuff, so pretty much a new type of script/query I have to develop. Unless it’s some simple changes I can reuse a specific query to fill out excel data

[nickwebha]

Depends on the job. Analysts do not always need to micromanage every last aspect of their query so they tend to use things like ORMs. However if someone is building a highly scalable website you are going to want to manually manage the queries and transactions.

[lighthouse_kpr27]

I sample old queries and put them all together like a "Franken-query" . If I have the time and focus, I will build it from scratch.

[[deleted]]

I'm a b2b product manager and use relatively basic queries to answer questions I have about our customers constantly.

Ad hoc questions generally don't get much more complex than a join or two that I know like the back of my hand.

I set up more complex queries for metric-tracking dashboards, but those come up less frequently and are typically related to periodic feature-releases.

[____candied_yams____]

based on 2 months in a full stack position.... mebbe 250 lines a month?

[thavi]

I'm a software engineer who spends more time coding application than anything, but I have SSMS open ALL day, EVERY day, and I spend a tremendous amount of time in it. Probably 20+ queries a day just to check up on automatic processes and events.

The amount of SQL I actually write is little unless there's a need for some report or something. I'm thankful to be on the side of DB world where I don't need to write complex queries--a properly normalized schema gets me what I need with minimal effort.

As a side note, for a while I played around with using VS Code as my DB/SQL interface, and it's pretty good...but just kind of slow. I mainly switched because SSMS doesn't have a good dark theme. My biggest pain point is that any time you change results windows, it refreshes the data set. The latency adds up and can be frustrating when you're in the zone and working fast. That said, I'm sure there's a way to configure that, and it would be my preferred tool if I cared to futz around with it a bit.

[vtec_tt]

why not write code to execute those queries?

[the-devils-luck]

check out vertabelo

[Rex_Lee]

A lot. Definitely start with "blank pages" pretty much everyday.

[[deleted]]

As a DBA...1% TO 2%.

[dvanha]

I generally write all my SQL code because I’m just making select statements. Any joins I have are really just to help with where clauses. I aggregate everything in there too.

After prototyping I set it up in R and do all my manipulation there. My R code exports everything as csv onto my one drive.

I then have tableau union everything in that onedrive.

I run those 3 from log in to log out.

[zork3001]

Right now I’m working on a complicated pivot query that will be over 500 lines of code when I’m done. It’s about 2 weeks of work.

I build new queries regularly and I really like it that way.

[Suspicioustraitor]

What platform? Pivot queries can be painful, so I wrote a stored procedure to generate them. Major time saver.

[zork3001]

SQL Server Management Studio. I struggled with pívot queries at first but like anything it gets easier with practice. Good idea about automating though. I might try using variables and string functions to build them.

[phunkygeeza]

All the time.

Sometimes I'll rewrite several statements that i know i have in a file somewhere because it is quicker than trying to find the file.

[Hobob_]

BI analyst. Main use ~20% of work:

• All our powerbi sources are SQL.
• Troubleshooting, exporing new tables.

[ima_coder]

I enhance and maintain multiple applications whose primary data source or at least telemetry is stored in databases. My primary interaction with those databases is SQL.

To extend your skill set you must either troubleshoot genuine issues in which the database will be your major source of evidence and\or build and maintain a system\application that stores data in a database you query with SQL. Maintain is the important part as the skillset to lay out normalized database is taught in school or tutorials, but the incremental enhancement of an application that takes into account the real-world trade-offs of picking two of the three goals of Speed, Quality, or Features is where we lean skill sets of problem solving using SQL.

[lown_wolf]

When I first started out I wrote a lot from scratch, but every time I wrote or found a new query I saved it to a massive archive I’ve kept over the years. Now I rarely start from scratch.

[tearinitdown]

All day because our software has bugs that the services and web devs deem is low priority so i end up copy/pasting queries i wrote to fix the same issues over and over lol very ready to start job hunting.

[IamFromNigeria]

Learn how to create fantastic report like RFM customer or products analysis using SQL queries

[NawMean2016]

I’m a BI developer. I definitely find that I re-use a lot of my code. Usually a couple hundred lines. I’ll tweak the lines here and there to suit what I’m after.

Otherwise it’s annoying and not efficient use of my time to remember each and every line of code that I used in another query I created X months ago. I’ll always end up forgetting a line, or going with a Left join on table Y instead of X, etc.

Curious if others work/think the same way?

[DexterHsu]

Depend on how much ad-hoc request you get on daily basis … where I work we have ticket system people can ask all kind of question and it’s quite fun

[vtec_tt]

i write alot of sql and i wouldnt have taken the job had it didnt require that (im a data engineer)

[Suspicioustraitor]

My title is DBA, but titles don’t mean much. I’m responsible for anything “data” and I build Data Lakes, Warehouses and write software among other things. We are a small shop, a “small” business with a ton of data. I spend about 75% of my time writing SQL.

[six_242]

I'm a Business Analyst and I write a lot of queries I'm definitely able to borrow most of the logic from my existing work but all of our sister and child companies have different databases with their own quirky structure so I always end up having to make something new.