Summarize Query Results by Week

When writing queries to track your metrics, it is common (and easy) to summarize your data by date. If you have a timestamp column (“2016-01-01 00:00:00”), there are built in functions to remove the time component. In MySQL and Postgres the date() function will do that for you. Using this method in the group by statements makes summarizing data very easy.

However, when you want to group your data by week, there is no easy built in method that will allow you to generate a human readable, group-by compatible date stamp.

The Bad Way:
The first instinct may be to want to generate a year week string (“2016 W1”). This is easy to do with a string concatenation call on two date components. However, its much better to still generate a valid date stamp for one simple reason: summarizing data like this is mostly being done to graph the numbers. Most charting libraries or tools do not support the “2016 W1” string. This is not a problem if you have at least one data point for ever week (no gaps in the data). If you do have gaps in the data, where nothing happens on one week, you will not be able to graph a timeline correctly and you will be looking at a distorted view.

The Better Way:
To ensure that your data will always render correctly in a timeline view, it is much better to determine a date transformation like the built in date() method but to determine one particular day of the week (start or end) and group by that value:

  1. Get the date stamp from the timestamp object
  2. Add X days to date based on the day of the week that date falls on
  3. You are done!

MySQL:

// Start of Week (Monday)
DATE_ADD(date(created_at), INTERVAL(-WEEKDAY(created_at)) DAY)

// End of Week (Sunday)
DATE_ADD(date(created_at), INTERVAL(6-WEEKDAY(created_at)) DAY)

Postgres / Redshift:

// Start of Week (Monday) 
date(date_trunc('week', created_at))

// End of Week (Sunday)
DATEADD(day, 6, date(date_trunc('week', created_at )))

Thats all there is  to it. The same concept can be applied to group by full months, years, or quarters and still just use a plain date string as the aggregator. This ensures you will always be able to throw your data into a charting library and easily generate actually useful timelines.

SheetDash + Redshift

SheetDash shipped with support for only MySQL and support for Postgres followed shortly. Since Amazon’s Redshift is based on Postgres, it was only a matter of time until SheetDash would support Redshift as well. Using the already available Postgres adapter for data sources within your project, you can now connect to your Redshift cluster.

What is Amazon Redshift and how does it help me? Redshift is Amazon’s solution to unifying all your data in one place so that querying across all your data sources becomes an absolute breeze. You can set up integrations from all your databases, Salesforce, Zendesk, and many other 3rd party platform to appear within one database in isolated schemas. Redshift lets you query across all different schemas to join up data for unprecedented insights and it is now available to power your dashboards directly via SheetDash.

Want to learn more about Redshift? For the best details on how Redshift works, go straight to the source and read more on the official site for Amazon AWS Redshift. Fair warning: it’s not cheap.

Want to start leveraging Redshift but don’t want to write your own integrations? Redshift can be painful and expensive to get started with if you choose to write your own integrations. Plus you would have to maintain all those syncs. If you would like to get started quickly (and have some money to spend) take a look at www.fivetran.com. They offer a SAAS solution to piping your data from many 3rd party sources directly to your Redshift cluster.

SheetDash Blog: Build Metrics Dashboards In Minutes

SheetDash.com is a simple software platform that allows anyone with a handful of SQL queries, access to a database, and a spreadsheet program such as Excel, Google Sheets, or Tableau’s Vizable app to generate powerful, always up-to-date dashboards in minutes.

The purpose of this blog is to allow users to get the most out of this tool. This will be composed of instructions to get going quickly and tutorials to push your SQL skills to new levels so you can answer any data question for your business (or personal project). The blog will focus on the following topics:

  • SheetDash Tutorials
    learn more about leveraging the tool in new ways
  • SQL Tips and Tricks
    pick up some new tools to answer any questions
  • SheetDash Compatible Tools
    leverage your data in more places (especially on mobile)