>99% of software "engineers" cannot properly use SQL nor design a proper relational data model.

I think the answer to the currency conversion problem is a temporary table, something that is hyper-criminally underused by novice developers. Create the temp table in the DB session, fill it with the current conversions, then join on that table in the aggregation query. If multiple aggregations occur, the same table could be re-used as well. The total number of currencies is around 200 meaning the insertion is trivial, and the table could be indexed with something lightning quick like a hash index to assure quick lookup.

I do something similar with currency conversion. My application periodically queries conversion rates on all the currencies we support and caches the data, then we just calculate the exchange and inject the values into the SQL query. No need for adding functions to PG/My or anything fancy. A caveat for this method is wildly fluctuating currencies like Zimbabwe or Venezuela that may become outdated very quickly, but our use case only requires major currencies like USD, CAD, AUD, GBP, EUR, CNY, JPY, THB, and other European ones, so mileage may vary. Sometimes the simple option is the best, like now NASA spent millions developing a pen that can write in zero-G, whilst the Soviets used pencils.

Some unsolicited advice: be extremely wary of putting business and calculation logic in SQL - databases generally don't scale horizontally and by the time you have that problem you've invested too much into that approach to undo. But nice video, SQL is very powerful and it's well worth learning how to use it efficiently.

Great video! I remember the first time I used window functions to solve a use case it blew my mind. However there is a danger in using SQL for business logic in your application which is that if you decide to migrate to a different database, all of your business logic is not as portable as it would be if it were just in Java for example (especially when most of your business logic is in the form of stored procedures). Yes most databases use "ANSI Standard" SQL syntax, however there's almost always some nuanced implementation details with how your queries will perform based on things like physical table modeling just as an example. And the moment you stray away from the ANSI Standard spec you now open up a can of worms, for example "CREATE MATERIALIZED VIEW" in Postgres doesn't exist in MySQL. And depending on how often you use that type of syntax, you might have quite a bit of reworking your code to do. Certainly not advocating that people don't use SQL, just mentioning to use sensible caution because I've seen it take 1+ years of unfucking everything when a company decides to move off of one database for another (specifically in the data warehousing space).

Amazing video keep doing it this way

This is why im glad for my couple years experience as a SQL dev. Im no expert or super guru or nothin, but just those couple years of business analytics have helped me think about data way better now that im transitioning to data science

Great editing style and very concise and useful information. I subscribed for your Protobuf video, nice to see you back!

What is the classical piece in the background? Sounds like something from baroque.

I guess I'm lucky I had to write SQL queries for reports before ever doing CRUD.

All you have is python 2? Thats *absurd*, its been deprecated for new development for like a decade...

for that trades example, just create an extra table with the currency, then write a c function for the convert. Only call the api to update the currency table daily or whatever.

We always knew about subqueries and functions/procedures in SQL, just that the time those take is way larger than just getting the data and processing it in our programming language of choice.

I get that it's an example but for most use cases it'd be better to keep a table with the daily currencies and you call that function to insert new rows when the current day isn't found, instead of calling an external API whenever the query runs. But hey, I had no idea you could integrate Python into SQL which was really neat to see.

I remember writing subquery problems for a subject final, doing this on a piece of paper is not very fun imo

I have a problem, i always use WITH instruction and i was doing a similar problem, but at the last part, ineed to filter the temporary table with the Max value, and it's way slow, like 0.5s, the temporary table has like 5rows, so the join at worst gives me the same 5rows; my tables already have indexes but a temporary table cannot have, doing the query in any other way runs faster than using the WITH instruction, any idea why?

If that would be me, in case of currency on the backend I would rather pull conversion rates first (with possibility of caching it if this endpoint call frequently), then during request I would use CTE like: WITH "conversion_rates" ("currency_from", "currency_to", "rate") AS ( VALUES ('USD', 'EUR', 123), (...) ) and then use it in expression in other CTE/subquery

For first cte you could use subquery

Great video but I'd appreciate less generic filler visuals. I'd actually prefer still sql screenshots or graphs made in paint than those "slick" moving images.

Think you can simplify the average trade query using "having" keyword. I personally try to avoid named subqueries in all but the most complex queries.

Wait - and we aren't talking about recursive queries?

python2 in 2023... and why is it py3 will be more complicated option?

Just get the exchange rates every hour/min/sec and insert them in a table, which is joined in the main query. Making API calls from SQL is stupid.

Instead of SQL people ask DSA, so people learn DSA and apply DSA.

Any Django people here?

The fact that subqueries and CTEs are not common sense sounds frightening, to say the least.

I see, you use squeel

biggest sin in sql 1)not using sql, 2) ORMS

Lol I thought you were about to say SQL as "squeal"

The more I look at SQL the more it makes sense for it to be integrated in the actual programming language but nah.... passing strings to a black box is the way!

making rest api requests from DATABASE ? hard pass...

The stock videos are distracting. Does your audience really have the attention span of a 4 year old, so you have to keep their attention with useless stock videos?