
Medium-hard SQL interview questions - thomzi12
https://quip.com/2gwZArKuWk7W
======
minimaxir
SQL interview questions are an interesting counterpoint to stereotypical
programming interviews: while typical algo questions in SWE interviews tend to
test what's taught in academic contexts but have little real-world
application; the questions in SQL interviews are more practical outside the
whiteboard.

A weakness of these types of SQL questions however is that it's near
impossible for the interviewer to provide help/guidance; the questions are
often know-it-or-don't questions (especially involving window functions w/
uncommon syntax). A data science interviewer years ago mocked me during a
whiteboard test for not knowing the BETWEEN ROW syntax for a window function.

That said, as an IRL data scientist, the amount of times I've had to do a SQL
self-join in the past few years can be counted on one hand. And the BETWEEN
ROW syntax for a window function.

~~~
JohnTHaller
I've been using SQL for 24 years and don't think I've ever used BETWEEN ROW
before. Did read up on it now, though.

~~~
psaux
I wrote sql for years for a forex platform used by some of the top dogs
(literal). Pretty hardcore sql and had to be very fast, never used BETWEEN
ROW.

~~~
roel_v
What breeds of dogs use forex platforms?

~~~
guywhocodes
Fiat Retrievers

------
danbmil99
This is idiotic. Why in the world would testing for rote memorization of
something anyone can look up easily be a reasonable filter for talent and
experience in a programming role?

A friend of mine did numerous interviews at a large company, hours out of his
time and those of the interviewers, only to be caught up by some inane SQL
question asked by a know-nothing after the entire process of interviews had
been completed.

Why not ask about obscure regex expressions? Better yet, how about baseball
scores? Hair bands from the 80s?

It's time for the valley to get real about how to judge the merit of
applicants. The present state of affairs in tech recruiting is a joke.

~~~
sgustard
"Idiotic" is a strong word. The question is: if candidate A answers these
successfully, and candidate B does not, is there a reason to believe that
candidate A is a better fit for your SQL data analyst position?

You may say no, that any competent person can read the book and learn this
stuff. But in my hiring experience there are sadly oceans of coders who, say,
reach for 100 lines of buggy slow Java code to solve something that could be
done in 3 lines of SQL. Once you've hired that person they do not magically
turn into an efficient or self-aware programmer.

~~~
mellow2020
> there are sadly oceans of coders who, say, reach for 100 lines of buggy slow
> Java code to solve something that could be done in 3 lines of SQL

Such questions don't test for that, they test for rote memorization. I don't
learn things by heart I only use rarely, and certainly not to help you with a
problem I'm not causing and that such memorization doesn't fix.

------
deepsun
Checked just the first two answers:

1\. MoM Percent Change

It's better to use windowing functions, I believe it should be faster than
self-join.

2\. It seems that the first solution is wrong -- it returns whether "a"-s
parent is Root/Inner/Leaf, not "a" itself.

I'd instead add a "has_children" column to the table, and then it would be
clear.

Second solution works, but without optimization it's 1 query per row due to
nested query -- slow, but not mentioned.

~~~
deepsun
Answer 4 is very very bad, you're doing O(N^2) JOIN. It's not just slow, it
will fail on bigger data.

The question just screams for windowing functions, and cumsum is a canonical
example for them.

Sorry post author, you'd fail my interview :)

~~~
thomzi12
Thanks for the feedback! The first section is intentionally about self-joins
since they get asked about in interviews, but other people have brought up
that window functions are more efficient, so I'll add in those solutions as
well.

Sorry you feel that way! Thankfully my employer felt differently :)

~~~
beckingz
If it runs fast fast enough or the business can wait long enough, slow and
ugly is acceptable.

~~~
deepsun
That's exactly what I look against on interviews. Slow SQL queries tend to
work fine for some time, and as data grows, they start failing.

I'd want to hire a professional, who knows the perfect way of doing that, so
for them it would be as easy to make it right, as to make it ugly.

------
fnord77
I think for a lot of people, SQL is a skill that doesn't stick.

You learn enough to do the queries you need for your project, they work then
you forget about them as you work on the rest of your project. These skills
are perishable. Left outer join? Yeah, I knew what that was some time ago, but
not anymore

The days of dedicated SQL programers are mostly gone.

~~~
matwood
I disagree. I've been programming for over 20 years, and SQL has been one of
the only constants used at every job. I'm surprised how many other programmers
I run into who are don't know or are even scared of learning SQL. Which is a
shame because data will almost always outlive the program(s).

------
Lightbody
I think these are great. But I think there should be some representation
around locking / concurrency / deadlock topics. Those tend to be the hardest
because you can’t clearly recreate the right/wrong answer in a local test
environment. Speaking as a person who waited far too long in his career to
fully appreciate these topics, I wish I had been pushed to learn them much
earlier.

~~~
bigtechdataeng
I suspect more SQL is written to support analytics than online systems.
Locking and concurrency are used in a specific type of application, namely
oltp.

I learned how much when I joined a big tech company. The devs don’t write sql
unless processing logs in the warehouse. But everyone from PM to support to
data science and marketing all write sql.

~~~
thomzi12
To your point, I personally write SQL for analytics and product/business
purposes, not system monitoring

------
thomzi12
Hey, HN! Since I couldn't find a good resource online for the self-join and
window function SQL questions I've encountered over the years in interviews, I
made my own study guide which I'm now sharing publicly as a Quip doc. Would
love your feedback or thoughts!

~~~
beckingz
For cumulative averages why not use window functions?

~~~
thomzi12
Good catch -- I can add that in as an alternate solution. Thanks!

~~~
beckingz
Of course!

Now the thing to do is for me to recreate your list on github.

~~~
thomzi12
Yes -- making this a proper webpage with an embedded SQL editor like
selectstarsql.com does would be a good next step

~~~
beckingz
Are there any good ways to embed SQL in markdown? Like JupyterLab notebooks?

~~~
cbcoutinho
Yes, Azure Data Studio allows you to embed SQL in notebooks right out of the
box: [https://docs.microsoft.com/en-us/sql/azure-data-
studio/noteb...](https://docs.microsoft.com/en-us/sql/azure-data-
studio/notebooks-guidance?view=sql-server-ver15)

~~~
beckingz
Thanks! I'll have to check that out.

------
oyoun
I think SQL is a language to be known by every programmer. With the right
query, you may solve a problem that may take 100 lines in other languages.

It is so usefull, reliable and does not change every year.

~~~
TrackerFF
With the extreme popularity of pandas, I think a lot of Python programmers
would be amazed how clean and easy-to-read SQL queries look like, compared to
the (downright) mess that's being written to query pandas dataframes.

~~~
bradleyjg
Depends on the transformation. Dropping a couple of columns in a wide dataset
is extraordinarily ugly in sql vs anything else, for example.

~~~
zbentley
...how is it ugly in SQL? Don't SELECT those columns. Or, if you need to do
multiple things with the dataset sans those columns, use a CTE of a query that
doesn't SELECT those columns. Or a view.

It seems like SQL is only uglier if you're used to "SELECT *"ing, which is
asking for trouble--in SQL or when doing the equivalent with other row/column
data manipulation tools.

~~~
bradleyjg
I don’t see what the issue is with using something like spark’s drop over
using select.

------
arh68
For the second one, it seems most natural to reach for _exists_ , or something
(I have not tried this code..)

    
    
      select
        node
        , (case when parent is null then 'Root'
                when exists (
                  select * from tree c
                  where c.parent = node
                ) then 'Inner'
                else 'Leaf'
          end) "label"
      from tree
    

EDIT: also, in the fourth, it seems like you'd want to _partition_ the window
function, who cares about order. Something like

    
    
      sum(cash_flow) over (partition by date) "cumulative_cf"

~~~
combatentropy
A subquery in the from-clause is usually lighter than the select-clause. I
just so happen to have a table with this structure and hundreds of thousands
of rows. This way runs in half the time. It is surprising, in fact, that the
difference isn't wider, because Postgres's explain-command says it costs 1/100
as much.

    
    
      select
          t.node,
          case
              when t.parent is null then 'Root'
              when p.parent is null then 'Leaf'
              else 'Inner'
          end as label
      from tree t
          left join (select distinct parent from tree) p on t.node = p.parent

------
ryanisnan
Great article. I can't help but feel like SQL is a poor choice for some of
this stuff, though. More often than not, I find it much easier to pull the raw
data into memory, and use a higher level language to do these sorts of
queries. I am all for knowing the intricacies of SQL, as the cost for not can
be very high, but I'm curious for your opinion here.

~~~
bdcravens
For small datasets, pretty much any approach will work. Once you hit hundreds
of millions of records (which isn't even that big of a dataset), SQL still
performs well on pretty modest hardware.

~~~
yen223
What do you mean by SQL?

In my experience, once you're hitting hundreds of millions of records,
implementation details of your database engine will start to matter. A
database designed for transactional workloads like Postgres will start to
choke on aggregate and window functions, often taking minutes to run instead
of milliseconds. A columnar database like Redshift (which exposes a SQL
interface) will breeze through it without a sweat.

~~~
bdcravens
Of course - I was comparing SQL to loading all the data in memory and then
using one's programming language of choice to do the hard work. Even a poor
schema in a row-based database will outperform what I described after a
certain number of records.

------
zozbot234
No questions/examples featuring recursive CTE's? They tend to come up in
anything involving queries over trees or graphs. They're also a relatively new
feature where having some examples to show how they work may be quite helpful.

~~~
magicalhippo
Took me some time to wrap my head around writing a recursive query. Also had
to study the docs a while to get "merge into" to work right, for doing
combined insert/update.

------
S_A_P
I flip back and forth between deep diving in (my case) SQL Server skills and
.NET Manipulation. In the world I live, it makes the most sense to do set
based manipulation in SQL and logical entity based logic in C#. I work in a
unique enterprise niche that has about 4 options based one either java or
.net. Sql knowledge definitely gives you a leg up for complex reporting, and
there are cases where I love being able to debug super quickly when comparing
inputs to outputs. However, when I run into a SQL script that is 5000+ lines
long and have to debug it, I much prefer the .NET side of the fence. Should
someone ever come up with a bridge that gives you .NET level of visibility
into the active datasets in a SQL query I would pay them 4 figures without
question...

~~~
beckingz
5000 + line SQL scripts????

What would one of these do?

~~~
S_A_P
Legacy code from Powerbuilder days. But it does things like calculate month
over month inventory, Mark to Market value, Risk, or things related to Energy
and commodities trading.

------
namdnay
Very interesting. I never really "got" declarative languages, I remember a
very long time ago I was working with Oracle and you could see the "execution
plan" for your SQL queries. I kept wondering "why can't I build my queries
directly with this?" \- it seems so much simpler to my brain than SQL itself.

~~~
TurkTurkleton
You can't write query plans directly because you would have to manually take
into account a number of factors that the query planner considers for you
automatically, such as size of the table, statistics on the distribution of
values, whether there are indexes that could be used to speed the query, and
so on. Some SQL dialects (like Microsoft's T-SQL) do give you some ability to
influence the decisions the query planner makes, though, like forcing it to
use specific indexes, or forcing it to use scans or seeks.

~~~
marcosdumay
And then comes Oracle and insists on applying equality filters first, because,
duh, they are fast, and never take any of those other details into
consideration.

Honestly, Postgres does it right - you can enforce your query plan to any
level of detail you want.

~~~
paulryanrogers
Does PostgreSQL have plan hints now? I thought they were opposed to them for
fear they become unmaintainable and hard to read.

~~~
lfittl
If you really want to, the pg_hint_plan extension can be used for this -
though I would use it very sparingly, if at all:

[https://pghintplan.osdn.jp/pg_hint_plan.html](https://pghintplan.osdn.jp/pg_hint_plan.html)

(available on some cloud providers as well)

------
vasilakisfil
I always thought that I suck in SQL but if these are medium to hard then I am
not that bad actually.

~~~
chucky_z
SQL for Smarties is the book that has proven, without a doubt, I am bad at
SQL. [https://www.amazon.com/Joe-Celkos-SQL-Smarties-
Programming/d...](https://www.amazon.com/Joe-Celkos-SQL-Smarties-
Programming/dp/0128007613)

If you want to get undeniably good with SQL, this is the book.

------
gtrubetskoy
One problem with this article is the number of times the solution involves
COUNT(DISTINCT).

One of the best SQL interview questions is "Explain what is wrong with
DISTINCT and how to work around it".

~~~
0az
What is wrong with DISTINCT?

~~~
barbegal
DISTINCT generally requires the results to be sorted which has O(n^2) worst
performance so it can have a big performance hit on a query. It is best to
make your database structure such that queries only return distinct data. E.g.
by disallowing duplicates

~~~
namibj
If your sorting algorithm degrades to anywhere near O(n^2) in pathological
cases, you're doing something wrong. And even if it's just a kind of
timeout/operations-limit to detect pathological cases and just run an in-place
mergesort instead. Tail latency/containing pathological data is quite
important if there's any interactivity.

------
ridaj
The first few answers are unidiomatic where I work. Analytical functions would
be vastly preferable to self joins, especially in the case of the join with an
inequality that is proposed to compute running totals, which I assume would
have horrible performance on large datasets

------
hotsauceror
RDBMS platforms without function indexes means that some of these queries will
force a row-by-row execution over your entire table. Enjoy running SELECT ...
FROM a INNER JOIN b WHERE DATEPART(a.date, month) = b.whatever on a table with
500 million rows in it.

~~~
paulryanrogers
Thankfully even MySQL has them, at least as of version 8

~~~
hotsauceror
Yeah, I’m jealous of all the postgresql and MySQL people. SQL Server doesn’t
have them and this kind of query would cause pain.

~~~
matwood
As someone who used to use MSSQL extensively and is now forced into MySQL,
trust me, do not be jealous of the MySQL people. 5.7 fixed some of the most
egregious issues and 8.0 is adding some features, but the smoldering embers of
the dumpster fire are still around.

------
ineedasername
I think I'd be able to do all of these in my daily work, probably not as
efficiently, with minor references to syntax guides (I don't use window
functions often enough).

In an interview, presumably my logic would, hopefully, shine through minor
issues of syntax.

Where would that put me? Maybe "okay to decent" when dealing with "medium-
hard" questions?

I would fail utterly at DBA management SQL and stored procedures, my
responsibilities skew towards data analysis.

~~~
dmurray
Yeah, I'd be something similar and also wouldn't consider myself a database
expert. I think this question list is tilted towards "can you express this
complicated abstract transformation in legal SQL" than the practical day to
day considerations of building and working with databases.

------
xupybd
For me the hardest part of the first question is understanding the acronyms. I
think MoM is month on month. But MAU, no idea.

~~~
ziftface
I agree, it almost seems they were put there to add artificial noise to the
problem. It doesn't seem like you're selecting for the right skills here.

~~~
thomzi12
Sorry about that -- in real life you would just ask the interviewer what MAU
meant if they didn't spell it out.

The purpose was to make the questions more realistic, since at least in my
experience in data analyst interviews the questions are asked in the context
of actual business or product situations ... like company leaders, PMs, or
others wanting to understand trends in MAU.

~~~
ziftface
Totally understandable, I think the frustration is more towards companies that
try to make the questions a bit more obscure, definitely not your fault.

Thanks for sharing, I found it very insightful.

------
snidane
SQL is better than 99% of the nosql alternatives out there.

But one thing it falls apart are these time series data processing tasks.

It's because of its model of unordered sets (or multisets to be more precise,
but still undordered). When you look at majority of those queries and other
real life queries they always involve the dimension of time. Well - then that
means we have a natural order of the data - why not use an array data
structure instead of this unordered bag and throw the sort order out of the
window.

SQL realized this and bolted window functions on top of the original
relational model. But you still feel its inadequacy when trying to do simple
things such as (x join x on x.t=x.t-1) or even the infamous asof joins where
you do (x join y on x.t <= y.t).

In array databases with implicit time order both examples are a trivial linear
zip operation on the the sorted tables.

In traditional set oriented SQL it results in horrible O(n^2) cross join with
a filter or in the better case od equijoin in sort merge join which still has
to do two expensive sorts on my massive time series tables and then perform
the same trivival linear zip that the array processor would do. Which is also
completely useless on unbounded streams.

Also many stackoverflow answers suggest to use binary blobs for time series
data and process them locally in your favorite programming language - which
points at wrong conceptual model of SQL engines.

Is SQL really so inadequate for real life data problems or have I been living
under a rock and Kdb is no longer the only option to do these trivial time
oriented tasks?

~~~
3dbrows
Have you seen TimescaleDB?
[https://www.timescale.com/](https://www.timescale.com/)

~~~
snidane
I've seen it.

Most of these 'time series databases' are for processing of structured logs
and metrics to be plugged into you favorite system for monitoring site
latency.

Asof join is still an open issue in their issue tracker so it is not usable as
a time series database.

[https://github.com/timescale/timescaledb/issues/271](https://github.com/timescale/timescaledb/issues/271)

~~~
claytonjy
If AS OF is a dealbreaker for you, so be it, but I think TimescaleDB is trying
hard to be more than a "simple timeseries" aggregator like so many
competitors. We're using it to store hundreds of millions of complex IIOT data
packets, where a fully normalized packet adds dozens of rows across ~4 tables.

Having it be mostly plain-ol-postgres has been huge for designing and evolving
the data model in a way that I'd hate to do without "real" SQL.

~~~
snidane
I'm sure your product is great for many customers in their domains.

The asof and other time based joins are a necessity for just slightly more
complicated data processing than just a trivial aggregation at the tail of the
stream which is majority of use cases in IOT and real time monitoring.

Without that I can pipe my data to /dev/null giving me even better write
performance while also not being able to solve this common financial industry
use case.

~~~
claytonjy
Sorry, didn't mean to imply I work there, I don't; my employer uses Timescale
and I designed our schema.

Timescale has been quite performant for us fetching arbitrary past time
windows, but I'll stop the cheerleading there as I think mfreed covered the
details far better.

I'd certainly still like to see AS OF support!

------
iblaine
>After an interview in 2017 went poorly — mostly due to me floundering at the
more difficult SQL questions they asked me

Could be you dodged a bullet. A company with advanced interview questions may
have some ugly SQL code. For jobs that lean heavily on SQL, I expect
candidates to know things like windowing & dynamic variables, in SQL & an ORM
library. For SWE's, I feel basic SQL is fine.

~~~
redis_mlc
I agree. Using advanced SQL as a hiring signal would be silly in that it just
discards a lot of programming candidates for little benefit.

I'd be happy if they knew what EXPLAIN was, since that impacts production on a
daily basis.

Also, the OP is basically "fighting the last battle" again. Most interviews
don't filter out candidates based on SQL.

Source: DBA.

------
ollien
This is neat, but it would be really helpful if these examples included some
kind of sample output of what was expected.

~~~
thomzi12
Some of them have sample output but yes, it would be helpful to extend this to
all examples. Good feedback!

------
nogabebop23
The problem with hard SQL problems is that they are often one of two camps:

1\. use some underlying implementation detail of the particular RDBMS or
proprietary extension to the standard

2\. are essentially tricks, like the typical ridiculous interview problem
designed to "make you think outside the box". Yes, you can do almost anything
in SQL but often you should not.

I get the perspective here is data analysis where you probably need to know
more SQL than the standard developer, but I still feel you should be testing
for solid understanding of basics, understanding of the relational algebra
concepts being used and awareness of some typical gotchas or bad smells.
That's it. They'll be able to google the precise syntax for that tricky by
date query when you're not guaranteed to have data for every month or whatever
on-demand.

------
userbinator
A bit off-topic, but this is another one of those sites that show nothing
without JS, and looking at the source reveals something a little more amusing
than usual: content that's full of nullness.

~~~
mrspeaker
At least they do it with a bit of flair though: the thin blue "loading" bar
works without JS!

------
sk5t
I'd ding the over-use of CTEs, when subselects are often more appropriate and
better-performing. Kind of a "every problem a nail" thing going on here.

~~~
combatentropy
Do you mean subselects in the SELECT clause or in the FROM? Usually subselects
in the SELECT clause have been the source of dogslowness. Reorganizing them
into the FROM clause sped up queries 1,000 times. And I thought CTEs were just
a way of moving subselects from the midst of the FROM clause to the top of the
statement, where they're easier to understand.

~~~
sk5t
In the FROM clause -- although I understand future versions of pgsql will
address this, the "WITH foo AS ... " approach can be horribly wasteful,
materializing all the results, whereas "SELECT * FROM ( SELECT f(x) AS a FROM
baz ) qq limit 1" lets the optimizer do its thing.

------
cameronh90
Out of curiosity: what is the use case for SQL window functions in application
programming? Unlike most SQL, it doesn’t seem to reduce the order of the data
coming back from the server, nor do anything especially faster than can be
done on the client - and has the disadvantage of extra load on the database
(which is harder to scale).

Is it only useful for ad hoc/analytical queries, or am I missing something?

~~~
kthejoker2
Why do you think it's not "especially faster" than doing it on the client?

The data is already sorted, partitioned, in memory .. adding a moving average
calc or max value per category is certainly faster than fetching the set to
disk, recreating it in an intermediate structure and then calculating the new
value with all that partitioning, sorting, etc. to be redone client side.

You can certainly use them in app dev for things like:

* figuring out how many things are ahead of you in a queue * snapshotting (what's changed since the last time you were here) * comparing something to some other sample for outliers or inconsistencies

These may be quasi analytical still, but ultimately they can manifest as
properties of an object model like any other property to be developed against.

~~~
cameronh90
I suppose it depends on the language. Normally I’m writing in either Java, C#,
Rust or C - and in those I’ve never found that having the DB run a moving
average to be any faster, provided the result comes back in the right order.
Indeed, if I need to use another column in the result set, the deserialisation
overhead means it’s normally slower. For reference I normally write financial
time series processing code which is where window functions ostensibly could
be quite useful.

However certainly if your app is written in Python or Ruby I can see there
being a big difference.

The reason I mention analytics is more to do with the ease of scaling out the
DB than the nature of the queries. In busy OLTP databases usually one wants to
keep the work off the database because they’re difficult and expensive to
scale.

------
pier25
Off topic but... anyone knows what they used for the rich text editor?

It uses React but I imagine there is some other library like ProseMirror here.

~~~
WoahNoun
It's likely all/mostly custom. Quip is saleforce's Word/Docs competitor. (It's
pretty good, tbh.)

~~~
pier25
It rendered that doc pretty fast although editing is not enabled.

I've never used Office online but Google docs is terrible performance wise.

------
nurettin
This is more like "we want to make sure you can use recursive CTE" questions.
To add some variety to medium-"hard" SQL questions you could add some lateral
joins, window functions (especially lag if you want to get creative) and
compound logic statements in where clauses.

------
beckingz
For creating a table of dates, what are our thoughts on:

select * from (select adddate('1970-01-01',t4.i _10000 + t3.i_ 1000 + t2.i
_100 + t1.i_ 10 + t0.i) selected_date from (select 0 i union select 1 union
select 2 union select 3 union select 4 union select 5 union select 6 union
select 7 union select 8 union select 9) t0, (select 0 i union select 1 union
select 2 union select 3 union select 4 union select 5 union select 6 union
select 7 union select 8 union select 9) t1, (select 0 i union select 1 union
select 2 union select 3 union select 4 union select 5 union select 6 union
select 7 union select 8 union select 9) t2, (select 0 i union select 1 union
select 2 union select 3 union select 4 union select 5 union select 6 union
select 7 union select 8 union select 9) t3, (select 0 i union select 1 union
select 2 union select 3 union select 4 union select 5 union select 6 union
select 7 union select 8 union select 9) t4) v where selected_date between
'2016-01-01' and now()

This works in MySQL / MariaDB.

~~~
klysm
Oh yeah that’s super readable

------
mosburger
Worth noting that this isn't all ANSI-SQL... e.g. I'm pretty sure WITH is a
Postgres thing?

~~~
combatentropy
The WITH clause, otherwise known as Common Table Expressions, is in ANSI
SQL99. Common table expressions are supported by all of the major databases:
PostgreSQL, Microsoft, Oracle, MySQL, MariaDB, and SQLite. They are also in
certain minor ones, like Teradata, DB2, Firebird, and HyperSQL. Recursive WITH
clauses are especially useful.

~~~
matwood
Worth noting that MySQL only got CTE's in 8.0 while something like MSSQL has
had them since ~2005. The reason this is important is that something like AWS
Aurora only supports up to MySQL 5.7, and thus no CTEs. :/

------
gigatexal
Yup those questions are a stretch for me too! Love that though.

Where are the pivot and unpivot questions?

~~~
thomzi12
Hmm, haven't seen those a ton at work / in interviews! Would love to see some
examples though if you have any.

Mode's SQL tutorial uses SUM(CASE ...) and CROSS JOIN to mimic pivots:
[https://mode.com/sql-tutorial/sql-pivot-table/](https://mode.com/sql-
tutorial/sql-pivot-table/)

~~~
petepete
I think they're specific to Oracle and SQL Server. It's a pity because they
can be very useful.

PostgreSQL can do it via the tablefunc extension and there's `\crosstabview`
which is built into psql.

[https://www.postgresql.org/docs/current/tablefunc.html](https://www.postgresql.org/docs/current/tablefunc.html)

[https://www.postgresql.org/docs/12/app-psql.html#APP-PSQL-
ME...](https://www.postgresql.org/docs/12/app-psql.html#APP-PSQL-META-
COMMANDS-CROSSTABVIEW)

------
dzonga
some of the problems with SQL, is it was written to solve problems when
hardware was expensive. BCF, n all the normal forms etc. when doing analytics
you want a flat table that's it. & when working with a flat table for
analytics they're other tools better for analysis than sql e.g pandas. or sql
like language used by column databases. once you've a flat table, you no
longer have to do joins etc.

------
ojr
I’ve done my fair share of complex sql queries and complex data migrations,
asking about JOIN during a random interview is unfair unless documentation.

------
cryptozeus
It would be helpful to show output result for each.

~~~
thomzi12
Yep, Ollien made a similar point. Currently some of the questions have
expected output but not all.

------
anonfunction
The first solution for MAU has the wrong sign for the percentage change
column:

Previous MAU 1000 Current MAU 2000 Percent Change -100

~~~
thomzi12
Thanks, anonfunction! I've corrected it

------
revscat
Every time I see an article such as this it reminds me how much I deeply abhor
SQL. It is an ugly language, closer in feel to COBOL than something that can
at times approach elegance, like Ruby or Scala. With languages like those, you
can loo at your work after you are done and be proud of it beyond its purely
functional aspect. SQL never elicits a response beyond “the task is finished
and it does what I want”, typically with a “finally” in there somewhere.

~~~
WrtCdEvrydy
SQL is an abstraction and I wish there was something on top of it supporting
loops but that's why we have proper programming languages...

~~~
te_chris
Recursive ctl’s in Postgres?

~~~
barrkel
Recursive CTEs are great for expressing very poorly performing queries. They
might give the right answer, but after you've given up waiting.

If you've got tree or graph structures, you're better off denormalizing paths
or transitive closures, to reduce the amount of indirection required to answer
questions.

------
sadhana1234
goood one

------
dang
We've changed the URL from
[https://gstudent.quip.com/2gwZArKuWk7W](https://gstudent.quip.com/2gwZArKuWk7W)
to what that redirects to.

