Data cleaning with SQL: filling missing dates

Filling in missing dates is a common data cleaning task that can be a bit of a curly problem when taking into account different groups of data within a dataset.

To demonstrate this (and the solutions) I'll be using a table called page_traffic:

Using SQL to calculate how often and how many days a store's inventory is out of stock

Recently a Reddit user posted in r/SQL wanting to know how to calculate how often and how many days an item was out of stock.

I enjoyed coming up with a solution and …

Creating custom colour palettes in Tableau from the command-line

If you're anything like me most of the time Tableau's in-built colour palettes are more than suitable for the visualisation you're creating. That said, I also quite like to create my own custom colour palettes for use in Tableau.

Unfortunately in order to create custom colour palettes in Tableau you …

Reducing the size of GeoJSON files with geojson-shave

Giant GeoJSON files can be a nightmare, crashing your IDE, GIS software or browser (and potentially causing you to tear your hair out in frustration!).

I use GeoJSON files quite often so I decided …

Which Sydney LGAs have the lowest and highest unemployment rates?

The Australian government's Jobs and Skills Australia has an insightful dataset I recently came across - quarterly Small Area Labour Markets (SALM) estimates of unemployment and the unemployment rate, broken out by local government area …

Using Spotify's Web API to analyse my Favourite Rock playlist

Since 2015 I've been adding my most-liked rock songs to the aptly named playlist Favourite Rock.

I thought it'd be fun to use content metadata from Spotify's Web API to learn more about my …

Officially named places of First Nations origin in NSW, Australia

Recently I was …

Splitting a delimited string column into rows using Snowflake

Do you have a column in your Snowflake table that contains delimited strings that you want to split into individual rows?

For example, let’s say we have a table called countries_official_languages, which has …

How I created a Twitter bot that posts about science fiction books

As an avid reader I thought it would be cool to combine my love of literature and data to create a Twitter bot that posts about sci-fi books :

Specifically …

Where are Australia's existing/proposed oil and gas pipelines located?

Recently I was …