SQL & Database Wrap Up - August 2019

Hi! I'm Helen, one of your friendly mods looking after the #sql and #database tags.

At the end of each month, I'll be showcasing some of the great posts, projects and insights from the data community.

We began the month with a post from Rachel who taught us about the differences between views, a stored query that can be run on the fly to abstract away from the source tables. And a Stored Procedure, a stored query that allows us to pass in variables and can be run on a schedule.

Comparing SQL Views and Stored Procedures

Rachel Soderberg Aug 5 3 min read

#sql #database

Then Homam took us through a practical use case for CTEs, Window Functions, Aggregates, and Date Functions to visualise data. If you're looking for a project to really flex your SQL muscles, this is a great place to start.

Time Series Visualization using SQL Window Functions

Homam Aug 7 11 min read

#sql #database #dataviz #data

Muhammad then took us back to basics with a thorough explanation of relational database concepts and how to get started if you've never used a database before. If you have a junior analyst in your life or need a refresher, check out this post.

Understanding Databases (Relational)

Muhammad Aug 11 3 min read

#sql #tutorial #beginners

Ben, the Seattle Data Guy, has been posting loads of great posts in the last few weeks. My favourite is this post on using analytic functions, self joins, or an aggregate table to create a running total.

4 Ways to Calculate a Running Total With SQL

SeattleDataGuy Aug 12 4 min read

#sql #career #beginners

Next up, Shawn evaluated combinations of BI tools and AWS Services that work in conjunction with DynamoDB to deliver operational reporting. Each approach is judged based on ease of setup/maintenance, data latency, query latency/concurrency, and system scalability so you can make your own decision on tooling.

Tableau Operational Dashboards and Reporting on DynamoDB - Evaluating Redshift and Athena

Shawn Adams Aug 14 11 min read

#database #cloud

This is a post that I've been thinking about writing for a while, mostly because I wanted to see the Friends PIVOT!! meme again. Erik takes us step by step through how to structure a query and makes it easy to get started with this handy tool.

Simple SQL Tutorial: How to Pivot

Erik W Aug 16 3 min read

#sql #tutorial

Our next posts demystifies how to add IF-ELSE logic to a SQL query. The CASE expression gives us up to ten branches to work with and stores the result in a new column within the result window.

SQL 201: How to add IF-ELSE logic to SQL queries

Helen Anderson Aug 19 3 min read

#sql #data #database

Michael's post gave us an overview of timezone handling using built-in MSSQL functionality. Working with dates can be tricky but using the system tables makes things much easier to manage.

Localizing Dates In Sql Server

Michael Born Aug 20 2 min read

#sql #localization

That wraps up the review for this month. If you'd like to see more check out the #sql and #database tags or follow The Database Dev on Twitter.