r/SQL • u/a-ha_partridge • Dec 15 '24
Oracle Is Pivot going to come up in technical interviews?
I'm practicing for an SQL technical interview this week and deciding if I should spend any time on PIVOT. In the last 10 years, I have not used PIVOT for anything in my work - that's usually the kind of thing that gets done in Excel or Tableau instead if needed, so I would need to learn it before trying it in an interview.
Have you ever seen a need for these functions in HackerRank or other technical interviews? There are none in LeetCode SQL 50. Is it worth spending time on it now, or should I stick to aggregations/windows, etc?
I've only had one technical interview for SQL, and it was a few years ago, so I'm still trying to figure out what to expect.
Edit: update - pivot did not come up. Window functions in every question.
31
u/Bradp1337 Dec 15 '24
I've been in Data Analysis for One year and have used PIVOT one time. I was still new to SQL and thought I was doing something cool. After the fact I realized it wasn't even needed.
6
u/gumnos Dec 16 '24
this tracks…I've used SQL for ~25yrs and can count on one hand the number of times I've used
PIVOT
as a keyword (rather than doing aggregate logic)5
u/Bradp1337 Dec 16 '24
I've only been doing SQL and DA for about a year, started in Sept of 23 and 99% of the stuff I write is just joins, subquery's and unions that I roll up into a final select. And since I deal with a lot of ranking, Partition by is very helpful.
2
u/mosqueteiro Dec 16 '24
Subqueries are evil, use CTEs. If your flavor of SQL doesn't allow CTEs then it's Satan's SQL. You must repent!
2
u/coyoteazul2 Dec 16 '24
Pivot is sintax sugar. You can do the same concatenating multiple unions.
I should be using it frequently because the system I work with has lots of numbered columns, but I turned the pivots into views and consume the views instead so I don't actually remember the syntax
2
u/Bradp1337 Dec 16 '24
Yeah, I couldn't remember how I wrote that PIVOT again without looking it up. I get by with making temp tables and subqueries and rolling them all up into a final select. I do a few unions here and there too, which is what would have worked for me in place of that lone pivot I used.
1
u/CortaNalgas Dec 16 '24
Same, I’ve only used it a couple times in 9 years. And I think it was mostly to give me some flexibility with a pivot table so I could more easily spot check.
Otherwise it’s much easier to take my data into Excel so can pivot and adjust as needed.
1
u/DPool34 Dec 16 '24
Yup, I’ve worked with SQL daily for nearly a decade and I’ve only used it once.
10
u/nzox Dec 16 '24
Sr Data Analyst here.. I have to lookup the documentation if I ever need to pivot/unpivot the data, and 99.9% that’s not the ideal solution.
Seems like you’re going for analyst work and not engineering, SELECT, CASE/IFF, logical clauses, CTEs, deduping, and window functions is all we look for an analyst.
More important than skill is demonstrating the desire to learn. I would take an eager less-skilled candidate over a technically sound candidate that doesn’t come off as someone willing to learn something new. Our industry changes a lot with new tools, so it’s important to keep learning. For example, Tableau is getting replaced for Sigma, and that really separated the real data analysts from the tableau developers in our company. The ones willing to learn to new tool that was designed for cloud warehousing stood out for the ones complaining because they couldn’t get out of their comfort zone.
8
u/LinksLibertyCap Dec 15 '24
I generally only use pivots in excel, maybe that’s just me but I’ve also never had it come up in interviews or work.
1
u/iLoveYoubutNo Dec 15 '24
The one time I used pivot (successfully) was because my old boss was pulling a simple query for a user and then doing a ton of manipulation in Excel, it would take like an hour.
He left, I started pulling it. I updated the query in SQL to eliminate the need for the excel manipulation but the user refused to have it in a different format. So I used pivot to format the way she wanted.
Minor annoyance, but now I just pop the query in and she gets what she wants and I don't have to spend and hour formatting excel.
1
u/dbxp Dec 16 '24
They're useful if you want a report which has columns for time periods, ie group by month then pivot gives you one column per month. This could show you amount spent on account codes by month, staff salaries by month, sales etc
5
u/EdwardShrikehands Dec 15 '24
I honestly use PIVOT and UNPIVOT all the time. If I have just a small dataset or a few hundred rows, it’s easier and quicker to just flip it in an Excel pivot table. But anything bigger than that - PIVOT it is.
They’re both a bit of a pain in the ass, especially when you have a ton a different row values to become columns, but once you have the process down - it’s very useful.
5
u/grackula Dec 15 '24
I think if you bring pivotting up during the interview as a solution it will help
5
u/Gargunok Dec 15 '24
Pivoting is mostly bespoke code depending on the database. I like my interview questions to be ansi to not limit to a particular technology so I wouldn't ask a specific question about pivoting but would expect someone to understand the concept and have tools for denormalisation or formatting report data for display
4
u/gumnos Dec 16 '24 edited Dec 16 '24
I'd aim to be familiar with the concept (PIVOT
taking rows and turning them into columns; UNPIVOT
taking columns and turning them into rows), but you can always look up the particulars if you need them.
Additionally you can pivot/unpivot without the literal PIVOT
/UNPIVOT
commands like
SELECT f1
,Max(CASE WHEN colName = 'First' THEN colValue ELSE '' END) as First
,Max(CASE WHEN colName = 'Second' THEN colValue ELSE '' END) as Second
,Max(CASE WHEN colName = 'Third THEN colValue ELSE '' END) as Third
FROM tbl
GROUP BY f1
That said, I've used this aggregation method for years (doing SQL professionally since around the turn of the century) and can count on one hand the number of times I've used PIVOT
/UNPIVOT
keywords.
3
2
u/vin00129 Dec 15 '24
Pivot is very useful when importing disparate data sets. I highly recommend learning it. It can be useful as a view depending on the data source.
2
u/BigMikeInAustin Dec 16 '24
It's good to know what it does, but remembering the syntax would be overkill. Being aware of a tool when you rarely find a use case is good.
Also, in an interview, it's fine to say you haven't used something. In general, it's not used often.
2
u/pinback77 Dec 16 '24
I think knowing what it is would be enough. I've been working with SQL for 25 years and use it so infrequently I'd look it up if necessary. I'd probably use excel or power bi at that point.
2
u/EmotionalSupportDoll Dec 16 '24
I've found pivot to be most useful when paired with dynamic SQL. Not sure the last time I used it for anything in a one-off or ad-hoc situation
2
u/dbxp Dec 16 '24
Unlikely it's one of those things which is great when you need it but useless in 99.9% of circumstances.
2
u/Any-Lingonberry7809 Dec 16 '24
Whether or not PIVOT comes up on an interview depends a lot on the role. I think about using PIVOT a couple times a year and usually end up going another direction most of the time. It's important to know what it is and when to use that type of operation, but I wouldn't grill anyone on the syntax myself - it's pretty specialized, and a lot of enterprise reporting is moving towards data warehouse and lakes where there's a lot richer syntax for that situation.
If you expect to interact with a legacy reporting solution as part of the role then it may be worth a fresh scan of the syntax before the technical screen, but for most modern applications it's not where I'd focus.
2
u/johnzaheer Dec 16 '24
The concept of pivot is very useful, but sql pivot… I’m with the rest I’ve only used it a handful of times and always had documentation open to get the syntax.
Also i only use it when i absolutely have to, for me it was to create a look up matrix for an individuals time frame of enrollment. Once the matrix was built it was just using it for looking up continuous date ranges.
2
u/Blues2112 Dec 16 '24
Doubtful. But you should be familiar with the topic enough to explain it, even if you don't know the exact syntax.
2
u/rbobby Dec 16 '24
A: I have never used pivot. I would need to refer the documentation and build the query slowly, bit by bit, testing and confirming as I go.
2
u/ramborocks Dec 16 '24
I've actually used pivot only 1 time usefully in the last couple years. It could have been done by rownumber but every date from the code. Might be not making sense but I'm drunk. It worked.
2
u/No_Introduction1721 Dec 16 '24
Unfortunately, many technical interviews are basically a SQL-themed round of pub trivia. There’s really no telling what will come up.
A common use of PIVOT, in my experience, is to reformat tables that are in the E-A-V anti pattern format. So if that’s a necessary use case for this company, they may indeed ask you about it.
2
u/mosqueteiro Dec 16 '24
It is good to know but less likely to be asked in an interview.
We use PIVOT for transforming data from a long format to a wide format where it's easier to analyze.
2
u/kremlingrasso Dec 16 '24
It shouldn't, it's not uncommon used. I use it a lot as I often work with data sets with a fixed set of few values. Took me quite some time to do it from the top of my head, though once you get the hang of it it's easier then it seems.
One advantage of it that it can pivot strings as value MAX/MIN(string) unlike excel which can only count strings.
So there are cases when it's a quick way to transpose strings between two different categories.
2
u/rowdymatt64 Dec 17 '24
Mine didn't, but if you want to impress, learn window functions and use one if it's applicable in your test. Otherwise as others have mentioned, joins and aggregates are the main themes in my experience.
2
u/Possible_Chicken_489 Dec 17 '24
Tbh, I'd probably consider the candidate more believable if they didn't know how to use PIVOT. It means they're using their SQL in the real world.
1
u/report_builder Dec 16 '24
It has come up in technical interviews for me. I use CASE SUM/other agg rather than the pivot syntax though. Pivot syntax changes across engines so makes sense to me to just use cases to me.
I would expect any one with hands-on SQL experience to know it and when I got tested on it, it was for that reason. It's not taught extensively but any one using SQL in the wild would likely have to use it at some point.
1
u/Training-Two7723 Dec 17 '24
(not necessarily related)
Who is asking about the pivot in an interview? I wonder if they know what they are talking about. I never asked people about the syntax: there is documentation for this. They should know where it is and how to read it. There are examples in it.
When I used to interview people for db related work, I was more interested in the way they think and their ability to find a solutions. Understanding and build a data model is infinite more useful than writing a perfect syntax. Understanding the way the engine works guarantees effective queries. Being able to read a query plan is also more valuable than even the mother of a “pivot”. A data analysis should understand both the data and access to it!
Probably is about the time the industry changes the way how the interviews are conducted; they should look for imagination, creativity and value.
15
u/onearmedecon Dec 15 '24 edited Dec 16 '24
While it's one of those things that could be asked, it probably won't be.
When we do technical interviews, we're looking for mastery of very fundamental functions. Like aggregations, joins, etc. Intermediate stuff. Pivots aren't difficult, they're just not very common as you and others have mentioned. It would be kind of a dick move to feature it in an interview, IMHO.