SQL and Database Wrapup (4 Part Series)
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.
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.
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.
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.
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.
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.
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.
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.