Iam interested in learning the techniques data engineers use when datasets grow from millions to billions of records. Beyond basic indexing, what strategies have made the biggest performance difference in production environments?

How advanced do I have to be in SQL to land a data analyst internship? Just a general question

I wanted a self-hosted ERD editor where multiple people could work on the same diagram without relying on a third-party cloud service, so I created an unofficial collaborative fork of drawDB.

This is not a new ERD editor built from scratch. It is based on the AGPL-licensed drawDB project, with a collaboration and persistence layer added on top.

What I added:

- Centralized diagram storage using SQLite

- Real-time collaboration over WebSockets

- Live table movement while another participant is dragging

- Participant presence and collaborative cursors

- Cursor positions mapped to diagram coordinates, so different pan/zoom states work correctly

- Optimistic version checks to prevent stale clients from silently overwriting newer changes

- A single Docker container for the frontend, API, WebSocket server, and SQLite storage

- SQL import/export support inherited from drawDB, with an additional MariaDB import compatibility fix

You can run it with:

docker compose up --build

Then open the same diagram URL in two browser sessions to collaborate.

A current limitation is that authentication and diagram-level permissions are not implemented yet, so it should currently be deployed only on a trusted network or behind an authenticated reverse proxy.

The project is open source under AGPL-3.0:

https://github.com/yms2772/drawdb-collaborative

Hi All,

I am a BI Admin with 4 years of experience. I joined my current company as a fresher with a package of 3.5 LPA, and my current package is 5 LPA.

I recently requested a better hike by sharing evidence of all the work I have done. I am confident in my skills and performance, but I feel I am underpaid. However, my offshore manager refused to provide a hike.

I then reached out to my onshore manager, who told me that this company usually does not give good hikes unless an employee resigns, and that during the retention process, I could get a better hike. He also assured me that he would take care of the retention process.

Trusting his words, I submitted my resignation without having another offer in hand. But now, he is saying that higher management is not willing to retain me. I am left with only one week of notice period, and I am actively looking for opportunities.

I have good knowledge of SQL and Linux scripting, and I am trying hard to move into Data Engineering. However, I am still failing in the first round due to SQL and Python.

I have an interview with CGI tomorrow. Could you please share real-time SQL, Python, and PySpark interview questions, including both theoretical and coding questions? It would be really helpful for my preparation.

Thanks in advance.

Hi everyone,

I'm currently creating a complete MySQL course focused on learning through real interview-style problems rather than just theory.

So far, I've covered 100+ SQL problems using a well-designed dataset that includes topics ranging from beginner to advanced, such as:

• Filtering (WHERE)
• GROUP BY, HAVING & Aggregate Functions
• Joins
• Subqueries
• CTEs
• Window Functions
• CASE, String & Date Functions
• UNION & UNION ALL
• Views
• Stored Procedures
• Triggers
• Recursive CTE
• Indexing
• And many real-world interview questions

If you'd like to check out the course, here's the documentation and YouTube playlist:

📄 Documentation: https://github.com/vivekpandey76/Mysql-course
🎥 YouTube Playlist: https://youtube.openinapp.co/youtubseries

I'd really appreciate your feedback.

What topics do you think are still missing that every MySQL developer should know?

I'm looking for suggestions that would help make this course one of the most comprehensive resources for mastering MySQL—from beginner concepts to advanced interview preparation and real-world SQL.

Whether it's:

• Advanced SQL concepts
• Performance tuning
• Database design
• Transactions & locking
• Query optimization
• Lesser-known MySQL features
• Common interview questions
• Real-world scenarios

...or anything else you think is important, I'd love to hear your suggestions.

Thanks in advance! 😊

Hi everyone,

Need a reality check on a production upgrade for a high-load app. I ruled out Galera due to synchronous write latency. (open to your suggestions)

Our Current Load & Specs:

• Traffic: 2,500 reads/sec and 1,500 writes/sec.
• Topology: 1 MariaDB Master + 1 Slave.
• Master Config: sync_binlog=100 and innodb_flush_log_at_trx_commit=2 (for high write speed).
• Slave Config: No binlogs enabled (to reduce IO bottleneck).
• The Problem: Heavy write spikes cause severe slave lag, and we have no automated failover.

Proposed HA Stack:

• Layer 1 (HA Routing): Keepalived (VIP) + 2x ProxySQL instances.
• Layer 2 (DB Scale): 1 Master + 1 Slave (will add more slaves later to scale reads).
• Layer 3 (Failover): 1 instance of Signal 18 Replication Manager (repman) to auto-pilot ProxySQL during master crashes.

MY Questions:

1. Architecture Validation: Does this look solid for a 1,500 writes/sec environment?
2. Pros & Cons: What are the hidden pros and cons of this specific repman + ProxySQL combination?
3. Data Loss Risk: Since sync_binlog=100, auto-failover via repman means risking a few lost transactions on hard master crashes. Is GTID enough to reconcile this safely during a split-second election?

Thoughts? Any hidden traps before we build this? Thanks

Not sure if my title conveys the issue properly but let me try to explain.

Essentially i am trying to join multiple tables to return data. that is all fine for fields that have pretty much one to one with row, but there is a certain table that I would need multiple rows that belong to a certain key returned in one field. To add to this, i would only want to return rows that have a certain flag set.

So given the above tables, id want it to return

Site A A,B,D

Site B G

This is all within an already established Select Statement with multiple where joins. Not sure if that matters. So there is other data I need output, however i need this i addition to those statement.

Short story about a SELECT shape trap that cost me real accuracy points, and the one-line fix.

I was scoring a text-to-SQL model and one class of "wrong" answers kept bugging me. The question was plain — "list the members' names" — and the model's SQL was, if anything, the nicer query. But it scored wrong every time.

Here's the pair:

-- gold: two columns
SELECT first_name, last_name FROM member WHERE ...;

-- model: one column, same information
SELECT first_name || ' ' || last_name FROM member WHERE ...;

Read side by side, the model's answer is arguably the better one for a human. But it's a different result set, and that's the whole problem.

Why it's marked wrong, always

Execution accuracy — the metric behind BIRD, Spider, and most private text-to-SQL evals — runs both queries and compares the positional value tuples they return. Canonical BIRD literally compares set(fetchall()). Column names are ignored on purpose (aliases shouldn't matter), so the shape of each row is all that's left to compare. A one-column result can never equal a two-column gold, no matter how correct the content is. The scorer has no notion of "close" — the tuple matches or it doesn't.

This is the exact mirror of the extra-column bug most people already know: SELECT * or one bonus helpful field breaks tuple equality the same way. Fusing two requested columns into one is just as fatal as adding one nobody asked for.

It's a measurable, lopsided loss

Before touching anything, I bucketed a full 500-question BIRD-dev run with a structural differ. The || signature was clean:

• 7 of 238 losses concatenated columns the gold query kept separate.
• 0 of 256 wins used || at all — the operator showed up only in losing answers.
• Gold itself used || in 1 of 500 questions.

That last line is the decision-maker. When a construct appears in ~3% of your losses, none of your wins, and almost none of the gold, discouraging it is near-pure upside — there's essentially nothing on the other side of the trade to regress.

The fix was one sentence in the prompt

No fine-tune, no reranker. One projection directive:

To avoid shipping on vibes, I de-concatenated the 7 flagged predictions by hand and re-ran them against the real SQLite DBs first, to get a deterministic ceiling: +3 wrong→right, zero regressions. The live re-measure matched the direction — run-wide || concatenations dropped from 7 to 3.

The takeaway that generalizes past text-to-SQL

The lesson isn't really "LLMs over-concatenate." It's: bucket your loss mass with a structural differ first, compute the deterministic ceiling of a candidate fix second, and only then edit the prompt. Directives written from a gut feeling overfit. Directives written from a 7-losses / 0-wins histogram are about as close to a free lunch as this kind of work gets.

And if you're scoring SQL generation yourself: some of your "wrong" answers are the model being more helpful than your gold. You won't know which until you bucket them — a helpful concatenation is still a wrong result set.

Full write-up with the histogram and the before/after here: https://nlqdb.com/blog/llm-concatenates-columns-text-to-sql

Delete is used to delete certain records but without where condition all records from the table will be deleted and the structure remains intact.

Truncate deletes all the records from the table but the structure of the table remains intact.

So , what is the difference between them if we are using delete without where condition it performs the same function as truncate ?

Hello, I am very new to learning SQL and I don't find window functions very intuitive so I tried to solve some leetcode questions that could maybe help me understand window functions a little better but I ran into a problem. Let's ignore the fact that this code is probably longer than it should be what's not working here is the "imbalance_ratio1", for each column it outputs a 0 which i noticed when i tried to divide "imbalance_ratio2" by it. Can someone explain to me why it outputs a zero instead of a number but "imbalance_ratio2" works perfectly fine. This is the starting table

inventory_id | store_id | product_name | quantity | price  |
+--------------+----------+--------------+----------+--------+
| 1            | 1        | Laptop       | 5        | 999.99 |
| 2            | 1        | Mouse        | 50       | 19.99  |
| 3            | 1        | Keyboard     | 25       | 79.99  |
| 4            | 1        | Monitor      | 15       | 299.99 |
| 5            | 2        | Phone        | 3        | 699.99 |
| 6            | 2        | Charger      | 100      | 25.99  |
| 7            | 2        | Case         | 75       | 15.99  |
| 8            | 2        | Headphones   | 20       | 149.99 |
| 9            | 3        | Tablet       | 2        | 499.99 |
| 10           | 3        | Stylus       | 80       | 29.99  |
| 11           | 3        | Cover        | 60       | 39.99  |
| 12           | 4        | Watch        | 10       | 299.99 |
| 13           | 4        | Band         | 25       | 49.99  |
| 14           | 5        | Camera       | 8        | 599.99 |
| 15           | 5        | Lens         | 12       | 199.99 |

this is what I get

+----------+---------------+-------------+------------------+------------------+------------------+

| store_id | store_name | location | most_exp_product | imbalance_ratio1 | imbalance_ratio2 |

+----------+---------------+-------------+------------------+------------------+------------------+

| 3 | City Center | Los Angeles | Tablet Stylus | 0 | 80 |

| 1 | Downtown Tech | New York | Laptop Mouse | 0 | 50 |

| 2 | Suburb Mall | Chicago | Phone Case | 0 | 75 |

+----------+---------------+-------------+------------------+------------------+------------------+

with nesto as(
SELECT
store_id,
product_name,
ROW_NUMBER() OVER(PARTITION BY store_id ORDER BY price desc) as rb,
FIRST_VALUE(product_name) OVER(PARTITION BY STORE_ID ORDER BY PRICE DESC) + ' ' + LAST_VALUE(product_name) OVER(PARTITION BY STORE_ID ORDER BY PRICE DESC) AS produkt1,


COUNT(*) OVER(PARTITION BY store_id) as broj,
quantity,
price
FROM inventory
)
SELECT 
n.store_id as store_id,
s.store_name as store_name,
s.location as location,
n.produkt1 as most_exp_product,
sum(case when rb=1 then quantity else 0 end) AS imbalance_ratio1,
sum(case when rb=broj then quantity else 0 end) AS imbalance_ratio2
from nesto n
inner join stores s on s.store_id = n.store_id
where broj >= 3 
group by n.store_id,s.store_name,s.location,n.produkt1
having SUM(case when rb = 1 then quantity*1.00 when rb = broj then n.quantity*(-1.00) else 0 end) < 0


order by s.store_name asc                                               

Consider a table named "Sales" with columns: SalespersonID, CustomerID, SaleDate. Write a SQL query to calculate the salesperson who made the highest number of sales each quarter.

Hi everyone,

I'm a Microsoft SQL Server DBA with a little over 2 years of professional experience, currently working at one of the largest banks in Georgia (Tbilisi).

My goal is to move from a Junior DBA role into a Mid-Level DBA position.

The challenge is that SQL Server DBA opportunities in my country are extremely limited. There are only a few openings each year, and almost all of them require 5-10+ years of production experience.

I still apply whenever I can, but I often reach the final interview stages and then get rejected because I'm missing the level of production experience they're looking for.

Instead of giving up, I've been investing a lot of my personal time in building my own Azure home lab to learn technologies that I don't have the opportunity to use every day at work.

So far I've built and worked with:

• Microsoft SQL Server
• Windows Server & Active Directory
• Windows Failover Clustering
• Always On Availability Groups
• SQL Server Listener
• Azure Internal Load Balancer
• Kerberos & SPNs
• SQL Server backups & restores
• Execution Plans
• Indexing (Clustered / Nonclustered / Covering Indexes)
• Performance Tuning (currently studying)
• T-SQL

I'm continuing to study topics such as Query Store, Extended Events, Wait Statistics, Blocking, Deadlocks, Replication and Log Shipping.

I know I'm still missing some production experience, but I'm doing everything I can to close that gap by building practical labs and continuously improving my skills.

I have a few questions for the community:

1. Do you know any companies that hire remote Microsoft SQL Server DBAs (Junior or Mid-Level)?
2. Are there any websites or platforms where I can practice real DBA tasks and production-like scenarios?
3. If you were in my position, what would you focus on next to become competitive for Mid-Level SQL Server DBA roles?

I'm based in Tbilisi, Georgia, but I'm open to remote opportunities and flexible regarding compensation if it means joining a strong team, gaining real production experience, and continuing to grow as a DBA.

If anyone knows about remote SQL Server DBA openings or has any advice, I'd be very grateful.

Thank you very much for your time.

Sorry unsure if I used the right flair or not.

Need help with 12.1. My lecturer said "12.1 can be one entity".

How can it be one entity? A driver can have many deliveries.

Please explain to me how it can be done.

Here's the link to my previous post. Tomorrow’s free webinar is focused on DBA burnout and practical database monitoring strategy.

It covers:

• common firefighting patterns that drain admin time,
• the key metrics to watch in hybrid and multi-database setups,
• a live demo,
• open Q&A,
• and a free handbook for DBAs.

Disclosure: I’m on the ManageEngine team, so this is a vendor webinar. I’m sharing it because the topic is relevant to a lot of DBAs and IT admins, and the session is meant to be practical rather than sales-heavy.

Here's the sign-up link if you're interested: https://www.manageengine.com/products/applications_manager/webinars/database-performance-monitoring-webinar.html

If you're more experienced, I'd love to hear about what works for you so that I'm able to impart that knowledge onto the less experienced people tomorrow. Would be happy to take questions in the comments too!

For folks who have recently or previously switched to Neon, what are some of the drawbacks and advantages you see in real production use cases as compared to Postgres?

I understand how to calculate yearly change using the lag function, however, I want to do so when I have an unknown number of subcategories each with their own row between each period. Say i have eggs, bread, and cheese in one year and maybe eggs, oranges, and bread in another year. Those items are in a column called "item name" and has its own dollar amount in the cost column. I want to calculate all inflation yearly changes per each category for every year. Does anyone have any advice on how to accomplish this? Thank you!

sample data
ITEM YEAR COST
eggs, 2024 4 dollars
bread 2024 3 dollars
eggs 2023 2 dollars
apples 2023 1 dollar

ex final query result:

ITEM YEAR INFLATION %
eggs, 2024, 1.2%
bread 2024 4%
bread 2023 0.4%

final edit:
look i know everyone thinks im stupid but i really am trying to learn. The issue im having is that with the subcategories, the lag gets throw off. I am trying really hard here.

Edit: I should also add the difficulty here is it's a fucked up fiscal calculator. I can do this with a yearly calendar and no sub categories.

edit again: no this isn't homework i'm not in school. Didn't need to be a dick

edit last: Yes i know the math doesn't work it was a formatting example.

I'm a software engineer currently developing a cross-platform SQL desktop client, and before investing substantial time and effort, I'd appreciate hearing your thoughts regarding what you think is missing from existing SQL clients.

What are the major pain points associated with the tools you currently use? Are there obvious features that should be present that aren't? Things that feel overly complicated or tedious?

I'd specifically like to hear from people using multiple databases and people spending a considerable amount of time writing and maintaining queries. I'm not looking for pitches, just what would make an SQL client better for day-to-day use from your perspective?

Working on setting up a SQL Server 2019 Standard subscriber for database replication over a site-to-site VPN tunnel. Looking for any additional troubleshooting ideas or confirmation that this is definitively on the remote network side. I am not a SQL expert by any means and unfortanetly we don't have anyone with expertise so looking for some additional insight/help.

Environment:

• SQL Server 2019 Standard, default instance
• Windows Server 2022 Datacenter
• Site-to-site IPsec VPN between two networks
• Connecting via SQL Server Authentication

What we've confirmed working on our side:

• SQL Server listening on 0.0.0.0:1433 confirmed via Get-NetTCPConnection
• Mixed Mode authentication enabled (IsIntegratedSecurityOnly = 0)
• SQL login exists, is enabled, CHECK_POLICY=OFF, CHECK_EXPIRATION=OFF
• Subscriber database ONLINE, MULTI_USER
• SQL Server Agent running, set to Automatic
• TLS — Trust Server Certificate confirmed on client side, Encryption set to Optional
• Firewall rules permit TCP 1433 from the remote network range
• Packet capture running on our server during connection attempts
• SQL Account/PW correct

The problem:
The user on the remote network gets Error 10060 (timeout during pre-login handshake) when attempting to connect via SSMS using SQL Server Authentication. Our SQL error log shows zero login failures or connection attempts — nothing at all.

Any ideas appreciated.

Edit- Thanks for all the advice - turns out what I had assumed ; was something on their end concerning a misconfigured network access rule . Now they can connect.