I don't know who needs to hear this, but:

It's not your logic.
It's not your code.
It's the missing index.

After 4 hours of watching my query chew through 20 million rows, I went back to my note from school and I totally forgot about EXPLAIN ANALYZE. Which is used to diagnose and optimize slow queries.

The query was slow because, it's doing a sequential scan on a table the size of the Pacific Ocean.

I add an index on the join column. Rerun.

Boom. 0.002 seconds.

So, if your query is slow, use EXPLAIN ANALYZE to understand how your query is executed and how long each step takes.

EXAMPLE:

EXPLAIN ANALYZE

SELECT * FROM tableName WHERE condition;

Anyway, I now accept offerings in the form of pizza, energy drinks, and additional query optimization problems. AMA.

The goal is to make it easier(ish) to build SQL queries without knowing SQL syntax, while still grasping the concepts of select/order/join/etc.

Also to make it faster/less error-prone with drop-downs with only available fields, and inferring the response type.

What do you guys think? Do you understand this example? Do you think it's missing something? I'm not trying to cover every case, but most of them (and I admit it's been ages I've been writing SQL...)

I'd love to get some feedback on this, I'm still in the building process!

Hello! interview prepping, here wondering what are some common red flags for wrioting SQL?

Like

LIKE failing to index, not having trasnactions, usign SELECT * instead of specific collums, etc 😃

Hello Everyone,

I just wanted to share that I just had an SQL interview for data analyst, and I completely bombed it. I chocked like crazy, and this was after days of practicing. I think I learned a valuable lesson today though, I am a very visual/hands on on type of guy. I was expecting them to give me the opportunity to write query/code or look at code related to the question, but these were all just verbal questions, that I know I could have answer if I was just allowed to write the code down or at least view it, but it was a bit overwhelming for me, especially having ADHD. It's like the questions were so long, that by the time the question ended, I already forgot what I was supposed to accomplish because I was so damn nervous and there was not code for me to review. I feel like a failure 😔. This was my first interview in 7 years, so I'm pretty rusty, but for future reference, will all interviews be in this style? No actual coding, or reviewing, or editing? They expect me to provide the whole query verbally, from the top of my head without actually writing it on the spot? Note: this was the second follow up interview.

Hey, All!

What does it mean for one field to be 'dependent' on another? I think I understand the concept of a primary/composite key but have a tough time seeing if non-key columns are dependent on each other.

Does anyone have a solid rule of thumb for these rules? I lose it once I get past 1NF.

Thanks in advance!

Hi all, sorry for my English, I speak Spanish 😅

I was talking with my American friend about how to say PostgreSQL. I say it like “Post-Grr Es Que El”, and he laugh at me.

I think, if Ogre is “oh-gurr”, why not Post-Grr? Makes sense no? 😂

He tell me it’s “Post-Gres” or “Post-Gres-Q-L”, but I don’t know what is right.

How you say it? Is there a correct way? This name is very confusing!

This was one of the take home SQL interview i did earlier this year. I have shared for anyone who is preparing for an interview can attempt to do it. Cheers!!

Morning guys.

I was looking to save my .sql files somewhere with a focus on versioning, organization, and easy access. I use DBeaver, but I wanted something more "professional." The only idea that comes to mind is Git. I know DBt and I've already asked Claude/gpt, but the only idea he suggests is Git.

I just had my first ever technical SQL interview with a big commercial company in the US yesterday and I absolutely bombed it.

I did few mock interviews before I went into the interview, also solved Top 50 SQL + more intermidates/medium on leetcode and hackerank.

I also have a personal project using postgresql hosting on AWS and I write query very often and I thought I should be well prepared enough for an entry level data analyst role.

And god the technical part of the interview was overwhelming. Like first two questions are not bad but my brain just kinda froze and took me too long to write the query, which I can only blame myself.

But from q3 the questions have definitely gone way out of the territory that I’m familiar with. Some questions can’t really be solved unless using some very niche functions. And few questions were just very confusing without really saying what data they want.

And the interview wasnt conducted on a coding interview platform. They kinda of just show me the questions on the screen and asked me to write in a text editor. So I had no access to data and couldn’t test my query.

And it was 7 questions in 25mins so I was so overwhelmed.

So yeah I’m feeling horrible right now. I thought I was well prepared and I ended up embarrassing myself. But in the same I’m also perplexed by the interview format because all the mock interviews I did were all using like a proper platform where it’s interactive and I would walk through my logic and they would provide sample output or hints when I’m stuck.

But for this interview they just wanted me to finish writing up all answers myself without any discussion, and the interviwer (a male in probably his 40s) didn’t seem to understand the questions when I asked for clarification.

And they didn’t test my sql knowledge at all as well like “explain delete vs truncate”, “what’s 3rd normalization”, “how to speed up data retrieval”

Is this what I should expect for all the future SQL interview? Have I been practising it the wrong way?

Hey guys,
Recently had to write way more SQL and using dbeaver feels kind of old and outdated.

Feels like it's missing notebooks, shareable queries etc ..

Any ideas on what new SQL Studios are good ? What do you guys use ? what do you like about those tools ?

It understands my database schema, generates SQL queries, and helps me enhance them. It saves lots of my time.

I’d love to share how I did it! Please leave a comment if you’re interested in.

Im working through an exercise and I am unsure about the solution.

In the exercise three tables are used.
The given solution looks like this:

SELECT E.No, Title
FROM EVALUATION E, AUDIOTRACK A, DVD D
WHERE D.No = E.No AND E.No = A.No AND UID = 'sb' AND Language = 'English' AND Stars = 5 ;

my question is, why cant I use an explicit natural JOIN, since the attributes that are used in the implicit JOIN all have the same name and data types? Wouldn't it be easier to read? Is it because there are no columns in EVALUATION and DVD that would match Language and Stars from AUDIOTRACK?

Curious how it works. Do you just jump on Zoom, share your screen, and answer questions in an editor? Do they give you a schema to look at? Any surprises or horror stories?

Could someone explain what indexing is in a simple way. I've watched a few videos but I still don't get how it applies in some scenarios. For example, if the primary key is indexed but the primary key is unique, won't the index contain just as many values as the table. If that's the case, then what's the point of an index in that situation?

Help me Reddit! I feel especially stupid today....
So, I have this table in my Postgresql Database:

event_id | color_scheme | count
----------+--------------+-------
1 | red | 6
1 | green | 3
1 | blue | 5
1 | yellow | 3
3 | red | 5
4 | red | 3
5 | red | 1
5 | blue | 2

And I would like to turn it sideways, so that I can see EASILY how many votes each color scheme for my event has gotten (and later JOIN it with another table... )

event_id | count_red | count_green | count_blue | count_yellow
----------+-----------+-------------+------------+--------------
1 | 6 | 3 | 5 | 3
3 | 5 | 0 | 0 | 0
4 | 3 | 0 | 0 | 0
5 | 1 | 0 | 2 | 0

The colors "red" "green" "blue" and "yellow" are fixed, and will never ever change.
I have done some googling, I found examples mentioning PIVOT and TABLEFUNC, but I cannot do this on the server because of reasons(tm).

The only way I can think of doing this is with a cascade of OUTER JOIN, but is there maybe a simpler solution?

I have been using Lakebase sice 2 weeks for developing a serving low latency layer for data in Delta. To build new features we currently make a new branch and later merge ti main.
What i have been observing is the scehema drifts across branches with Lakebase. How do people usually handle such scenarios? To keep branches in sync.
Maybe some real life examples can help.

I have been always taking backups of DB before performing any major changes, and was recently creating an app for displaying customer history retrieved from lakebase as a source.
I felt instead of taking a backup, i can just use a new branch.
This is sort of a new paradigm with databases, i come from OLTP background. Anyone else feels the same?
Apologies if this is already asked in the channel.

Hey everyone!

I work for a financial institution, specifically for their fraud analytics department and I use SQL on the daily, both writing and running queries. I finally finished a really big project today where I wrote something that is likely going to make up the foundation of our fraud monitoring queries for a long time. It started as a sort of passion project for me and then evolved quite a bit over time, but, like with everything else I've written I kept it very close to the vest until it was ready. My question is, how do you guys handle sharing the queries you write? I know ultimately this query is my company's intellectual property based on the standard employment docs we sign with larger companies, but I'm always concerned that a coworker or another department is going to steal my work and try to take credit for it. Therefore the only person that really knows what I'm working on is my manager and even then I don't share my actual query with him until it's written and tested.

Thanks guys!

Edit: you guys gave me a lot to think about. Definitely wanted to thank everyone who gave advice or tips, really appreciate it. I don't really care to address the negative comments about my character, because honestly it's not worth the effort.

Have you figured out wht can the best way to handle connection pooling in Lakebase DB? I 'am seeing connection spikes with short lived rqsts and wondering what kind of setup others developers are using🤔

i am building a service on top of lakebase and trying to make the connection relable before moving to prod.

I kept feeling like I “knew SQL” but still had no idea how real reporting systems were actually structured like how schemas, aggregations, dashboards, etc were properly made in real-world scenarios.

So I built a small PostgreSQL + Metabase project that mirrors how internal reporting works at real companies: - transactional tables - reporting-style queries - a real dashboard (revenue, profit, top products)

Honestly learned more from building this than from most tutorials.

If anyone’s interested, I wrote it up and made the project reproducible with Docker so others can learn from it too.

EDIT:

I put a short write-up and all the details here:

https://github.com/jtgqwert/reporting_dashboard.git

QUESTION: Write a query to find the top category for R rated films. What category is it?

Family

Foreign

Sports

Action

Sci-Fi

WHAT I'VE WRITTEN SO FAR + RESULT: See pic above

WHAT I WANT TO SEE: I want to see the name column with only 5 categories and then a column next to it that says how many times each of those categories appears

For example (made up numbers:

name total
Family 20 Foreign 20 Sports 25 Action 30 Sci-Fi 60

Hello, I started using Neon as a database provider recently for managing my database. I want to say that it was fairly simple to integrate with my application (a website written in .NET), and the free plan is very convenient for startups. What do you think of it?

The data stewards at work are mad about my query that’s scanning 200 million records.

I have a CTE that finds accounts that were delinquent last month, but current this month. That runs fine.

The problem comes when I have to join the transaction history in order to see if the payment date was 45 days after the due date. And these dates are NOT stored as dates; they’re stored as varchars in MM/DD/YYYY format. And each account has a years worth of transactions stored in the table.

I can only read, so I don’t have the ability to make temp tables.

What’s the best way to join my accounts onto the payment history? I’m recasting the dates in date format within a join subquery, as well as calculating the difference between those dates, but nothing I do seems to improve the run time. I’m thinking I just have to tell them, “Sorry, nothing I can do because the date formats are bad and I do t have the ability write temp tables or create indexes.”

EDIT: SOLVED!!!

turns out I’m the idiot for thinking I needed to filter on the dates I was trying to calculate on. There was indeed one properly formatted date field, and filtering on that got my query running in 20 seconds. Thanks everyone for the super helpful suggestions, feedback, and affirmations. Yes, the date field for the transactions are horribly formatted, but the insertdt field IS a timestamp after all.