The Olympics is over for another year. But there's still plenty of time for SQL-style data wrangling of the results! To do this, I've compiled a table of medal...
The Olympics is over for another year. But there's still plenty of time for SQL-style data wrangling of the results! To do this, I've compiled a table of medal winners from Rio for each sport This is great when looking for a specific result. But what everyone really wants to know how their country fared overall. To get this you need to convert the table above to the final medal table: To do this, you need to count the number of gold, silver and bronze rows for each country....
The Olympics is over for another year. But there's still plenty of time for SQL-style data wrangling of the results! To do this, I've compiled a table of medal winners from Rio for each sport This...
There are some things in life it's great to have spare copy of. Your keys, your kidneys and your backups to name a few. Image by Ryan McGuire But there are some...
There are some things in life it's great to have spare copy of. Your keys, your kidneys and your backups to name a few. Image by Ryan McGuire But there are some things you don't want duplicated. Rows in your database tables are a perfect example. So it's unsurprising a common question people working with relational databases have is: "How do I find duplicate rows using SQL?" This is often closely followed with: "How do I delete all but one of the copies?"
There are some things in life it's great to have spare copy of. Your keys, your kidneys and your backups to name a few. Image by Ryan McGuire But there are some things you don't want duplicated. Rows...
Imagine you have a table with a series of transactions. These are classified into credits or debits: TRANS_ID TRANS_DATETIME TRANS_TYPE AMOUNT --------...
Imagine you have a table with a series of transactions. These are classified into credits or debits: TRANS_ID TRANS_DATETIME TRANS_TYPE AMOUNT -------- ----------------- ---------- -------- 6 01-JAN-2015 00:00 CREDIT 40 7 01-JAN-2015 00:00 CREDIT 90 8 01-JAN-2015 00:00 DEBIT 80 5 02-JAN-2015 00:00 CREDIT 200 4 03-JAN-2015 00:00 DEBIT 99 3 04-JAN-2015 00:00 CREDIT 50 2 05-JAN-2015 00:00 DEBIT 200 1 06-JAN-2015 00:00 DEBIT 100 Your boss has asked you to do the following: "I...
Imagine you have a table with a series of transactions. These are classified into credits or debits: TRANS_ID TRANS_DATETIME TRANS_TYPE AMOUNT -------- ----------------- ---------- --------...
In the previous post we looked at stock market investment strategies. It looked like we'd hit on a winning method: buy $100 of shares every time the S&P 500...
In the previous post we looked at stock market investment strategies. It looked like we'd hit on a winning method: buy $100 of shares every time the S&P 500 falls 2% or more during a day. This provided the best return on our money. As hinted, there are problems with this method though. Let's find out what these are and see if we can mitigate them. We'll use SQL magic to do this, including a look at the model clause. First, let's review how feasible the 2% buy rule is. Our...
In the previous post we looked at stock market investment strategies. It looked like we'd hit on a winning method: buy $100 of shares every time the S&P 500 falls 2% or more during a day. This...
What's the best strategy for investing in the stock market? Simple - buy low and sell high. There's big a catch to this method. We don't know in advance what...
What's the best strategy for investing in the stock market? Simple - buy low and sell high. There's big a catch to this method. We don't know in advance what the highs and lows are! Only by looking back can we know the best times to buy and sell. So which approaches can we use? And, more importantly, how much money can we make by following them? In this post we'll use SQL to calculate stock market returns for different purchasing strategies. The purpose is to show how to...
What's the best strategy for investing in the stock market? Simple - buy low and sell high. There's big a catch to this method. We don't know in advance what the highs and lows are! Only by...
A colleague recently posed me the following question: I want to find a row matching a string and the N (non-matching) rows either side of it, ordered by primary...
A colleague recently posed me the following question: I want to find a row matching a string and the N (non-matching) rows either side of it, ordered by primary key. How can I do this using SQL? It's an interesting problem. Let's look at how you do in this in a single, efficient select statement. When constructing SQL queries it's always beneficial to have example data along with the expected output. In this case, given the following rows: ID STRING -- ------ 1 abc 2 def 3...
A colleague recently posed me the following question: I want to find a row matching a string and the N (non-matching) rows either side of it, ordered by primary key. How can I do this using SQL? It's...
Thanks to everyone who entered the Obfuscated SQL Contest! This ran on the PL/SQL Challenge from March - April. It asked players to produce ingenious,...
Thanks to everyone who entered the Obfuscated SQL Contest! This ran on the PL/SQL Challenge from March - April. It asked players to produce ingenious, creative, quirky and downright weird SQL to return the current time in the timezones Bangalore, London, New York, Chicago and Denver. There were 53 entries, many of which provoked serious head scratching amongst the judges to determine how these queries actually work! After much deliberating in an exceptionally close run...
Thanks to everyone who entered the Obfuscated SQL Contest! This ran on the PL/SQL Challenge from March - April. It asked players to produce ingenious, creative, quirky and downright weird SQL to...
