We’re not really big enough to claim that what we needed was a Data Scientist, but that is effectively what we were looking for. And as is our wont, we created a simple test for applicants, designed to show some ability to extract meaning from data (by creating a histogram), and a working knowledge of SQL. We do most of our running reporting through Periscope, so being able to generate flat datasets from our relational database is an essential part of the job.
Testing candidates is always revealing, but in this case especially so – out of perhaps 20 candidates, only two nailed it – the rest either got the wrong answer, or the right answer the wrong way (for example, joining the two tables in Excel, using a vlookup function.) This got me thinking – is raw SQL becoming a lost art?
Modern SQL in PostgreSQL [and other databases]
“SQL has gone out of fashion lately—partly due to the NoSQL movement, but mostly because SQL is often still used like 20 years ago. As a matter of fact, the SQL standard continued to evolve during the past decades resulting in the current release of 2011. In this session, we will go through the most important additions since the widely known SQL-92, explain how they work and how PostgreSQL supports and extends them. We will cover common table expressions and window functions in detail and have a very short look at the temporal features of SQL:2011 and the related features of PostgreSQL.”
100x faster Postgres performance by changing 1 line
SELECT c.key,c.x_key,c.tags,x.nameFROM context cJOIN xON c.x_key = x.keyWHERE c.key = ANY (VALUES (15368196), — 11,000 other keys –)AND c.x_key = 1AND c.tags @> ARRAY[E‘blah‘];
The MERGE Statement in SQL Server 2008
Sarting with SQL Server 2008, you can use a MERGE statement to modify data in a target table based on data in a source table. The statement joins the target to the source by using a column common to both tables, such as a primary key. You can then insert, modify, or delete data from the target table—all in one statement—according to how the rows match up as a result of the join.