SQL - Narrator Data Blog

Data Engineering

Handling slowly changing dimensions in an Activity Schema

The simplicity of modeling slowly changing dimensions in an Activity Schema vs a Star Schema

Data Engineering

Identifying bot traffic with SQL

Systematically identify bot users in your web traffic with a simple SQL query.

SQL

PostgreSQL's missing DateDiff function

DATEDIFF is an extremely useful function for analytical queries. Given two timestamps it'll tell you how many days, weeks, months, hours, etc they are apart. Here we show an implementation of this function for Postgres.

SQL

Using PostgreSQL as a Data Warehouse

With some tweaking Postgres can be a great data warehouse. Here's how to configure it.

SQL

Synthetic Data: Generating realistic user timestamps in SQL

How to generate timestamps in SQL that match realistic user behavior data.

SQL

How to generate a series of numbers in SQL

How to generate a sequence of integers for most data warehouses, including Postgres, Redshift, BigQuery, Snowflake, and Azure SQL

SQL

SQL Hack: How to join tables when the key is buried in a blob of text

The SituationText blobs are a pain, but they are commonly used with data from email platforms, Salesforce, or dynamic UTMs. In these cases, the JOIN key is probably hidden in a blob of text. For example, say you want to join your users table using the user_id from the

SQL

SQL Hack: How to get first value based on time without a JOIN or a Window Function?

This is my favorite trick in SQL! If you are like me and have been working with data for a while, you have googled “how to get the first value without a join or window function” multiple times in your life. We often hate having to write complex queries to