Mastering SQL Group By Week: A Full-Stack Guide

Grouping data by calendar week is a ubiquitous requirement in data analytics. As a full-stack developer, you‘ll often need to aggregate data into weekly buckets for reporting and visualization.

However, handling weeks in SQL can be deceptively tricky. Dates are simply stored as absolute points in time, with no innate notion of higher level periods.

In this comprehensive 2600+ word guide, you‘ll gain full-stack developer level mastery of weekly data segmentation in the SQL language.

Why Group Data by Week?

Here are the top reasons you may need to slice SQL data into weekly segments as a full-stack developer:

Analytics and Reporting

• Surface weekly trends over time in KPIs
• Build weekly reports for business users
• Compare weekly metrics across products, campaigns etc.

Feed Data Models

• Aggregate data into weekly partitions for machine learning
• Use weekly base metrics for predictive models

Visualization

• Plot weekly graphs in dashboard tools like Tableau, Power BI
• Display weekly aggregations in web UIs

By splitting date records into weekly units, we can:

• Summarize metrics, events, transactions etc. occurring in the same week periods
• Simplify data for analysis instead of daily grain which can be noisy
• Detect meaningful weekly seasonality signals

The Challenges with Grouping by Weeks

As a full-stack engineer, you need to deal with varied, messy real-world edge cases when handling weeks:

• Irregular shapes: Weeks can span across month and year ends
• Inconsistent distributions: Days per week can vary
• Gaps: Data can be missing in some weeks
• Skewed data: Volumes may fluctuate heavily across weeks

This makes accurate segmentation non-trivial. Basic methods like splitting by year and week-of-year can fail on edge cases.

Additionally, different systems can use Monday, Sunday or Saturday as the first day of week – adding more complexity.

In the next sections we explore production-grade techniques to solve these problems.

SQL Date Parts for Working with Weeks

SQL provides a set of functions to extract component pieces of date/time values:

SELECT 
  DAY(date) -- Extract day of month 
  MONTH(date) -- Extract month number 
  YEAR(date) -- Extract 4-digit year
  WEEK(date) -- Extract week of year
  WEEKDAY(date) -- Extract weekday number 
FROM tablename;

These extract and return just the specified portion of the date – extremely useful for grouping records by periods.

Do note capabilities and syntax does vary across database platforms like PostgreSQL, MySQL, SQL Server, Snowflake etc. Always check vendor documentation.

Now let‘s see how we can utilize these to group data by weeks.

Basic Approach: GROUP BY YEAR + WEEK

A simple way is to extract the year and week-of-year portions separately and group the data on those values:

SELECT
  YEAR(date_col) AS yr, 
  WEEK(date_col) AS wk,
  COUNT(*) AS ct  
FROM table
GROUP BY 
  YEAR(date_col),
  WEEK(date_col)

This breaks records into year + week-of-year buckets e.g. (2020, 45), (2021, 23)

Strengths

• Simple and fast
• Works for most use cases

Weaknesses

• Limited handling of edge cases
• May produce irregular week shapes or gaps

Let‘s walk through an example scenario.

Example Use Case

Problem: Analyze weekly trends for job application submissions on a platform.

Schema:

applications

id, int 
submitted_at, datetime
candidate_id, int
job_id, int

Query:

SELECT 
  YEAR(submitted_at) AS yr,
  WEEK(submitted_at) AS wk,
  COUNT(id) AS submissions
FROM applications
GROUP BY 
  YEAR(submitted_at),
  WEEK(submitted_at)
ORDER BY 1, 2;  

Output:

yr	wk	submissions
2020	45	573
2020	46	592
2020	47	510
2020	48	492
2020	49	628
2020	50	551

visualized weekly submission trend:

We can see submissions aggregated by year + week buckets. Lets you easily plot weekly trends.

This approach provides basic grouping but has some edge case limitations full-stack developers should be aware of next.

Challenges with Simple Group By Week

While simple and fast, directly grouping on week-of-year has inconsistencies around calendar year transitions and shifting day distributions in some edge cases:

Irregular Week Shapes

Weeks can sometimes span across adjacent months or years:

December 2020
Su Mo Tu We **Th** Fr Sa  
       29 **30** 31 Jan 1 **2**

So Dec 30th to Jan 2nd comprises a single week but falls across two years. This can throw off analysis.

Varying Number of Days

Day distributions per week can be inconsistent:

Week 1 - Jan 1st to Jan 7th -> 7 days
Week 2 - Jan 8th to Jan 14th -> 7 days 

Week 53 - Dec 26th to Jan 1st -> 8 days 
Week 54 - Jan 2nd to Jan 8th -> 6 days

Weeks contain varying numbers of days which skews aggregation volumes.

Gaps in Data

Sparse datasets may have missing data for some weekly periods:

Week N -> 12 records
Week N+2 -> 5 records 

This leaves analytical gaps.

To handle cases like these, we need more robust approaches.

Advanced Approach: Generate Surrogate Week ID

An alternative used in production analytics pipelines is to auto-generate a surrogate week ID field, assigning a unique sequential integer number to every week period in the data.

This enumerates all weeks as a gapless series of values irrespective of month/year transitions.

We can then simply group by this field for clean segmentation.

Here‘s one simple method in SQL to generate ids:

DATE_COL - INTERVAL ‘1 DAY‘ + 4 - INTERVAL EXTRACT(DOW FROM DATE_COL) DAY AS week_id

The logic:

• DATE_COL: The source date field
• - INTERVAL ‘1 DAY‘: Subtract 1 day – changes Sunday to Saturday
• + 4: Add 4 days – changes Saturday to Wednesday (midweek)
• - INTERVAL EXTRACT(DOW FROM DATE_COL) DAY: Subtract weekday number to pull dates to previous Wednesday

This shifts all dates to fall on a Wednesday boundary (midweek point) first. Dates by calendar week will now get the same id.

We can integrate this expression in the GROUP BY:

SELECT
  (DATE_COL + CALCULATION) AS week_id,
  AggregateFunction(metrics)
FROM table
GROUP BY 1

Very clean segments.

Example

Problem: Analyze weekly trends in inventory stock checks.

Schema:

inventory  

id, serial primary key  
last_verified, datetime

Query:

SELECT
  (last_verified - INTERVAL ‘1‘ DAY + 4 - INTERVAL EXTRACT(DOW FROM last_verified) DAY) AS week_id,
  COUNT(*) AS records
FROM inventory
GROUP BY 1;   

Output:

week_id	records
17797	5
17798	12
17799	8
17800	16

We get contiguous weeks now – solving edge issues!

Working with ISO Standard Weeks

Another robust option for full-stack developers is to leverage ISO 8601 standard weeks which number each week sequentially from 1 to 52/53 with solid mathematical logic.

PostgreSQL, MySQL, SQL Server provide functions to extract ISO week values:

ISO_WEEK_YEAR(date) -> Returns ISO week year
ISO_WEEK(date) -> Returns ISO week number (1-52) 

We can group by these standard values:

SELECT
  ISO_WEEK_YEAR(date) AS yr,
  ISO_WEEK(date) AS wk,
  AggregateMetrics(cols)
FROM table
GROUP BY 
  ISO_WEEK_YEAR(date), 
  ISO_WEEK(date)

This guarantees solid weekly segmentation even for awkward edge cases thanks to the ISO specifications.

One thing to watch out for is handling issues where weeks fall on year ends as the year portion can jump.

Example

Problem: Analyze weekly sales for an ecommerce site.

Schema:

sales

id, serial primary key 
sale_date, date
amount, numeric(10,2)  

Query:

SELECT
  ISO_WEEK_YEAR(sale_date) AS yr, 
  ISO_WEEK(sale_date) AS wk,
  SUM(amount) AS total_sales
FROM sales
GROUP BY  
  ISO_WEEK_YEAR(sale_date),
  ISO_WEEK(sale_date)
ORDER BY 1, 2;

Output:

yr	wk	total_sales
2019	48	58399.00
2019	49	99251.00
2019	50	109442.00
2019	51	40461.00
2019	52	85651.00
2020	1	75303.00

Standardized clean weeks!

Tip: Handle year transitions by including monthly granularity.

ISO weeks provide robust data processing. However calculation overhead can sometimes have performance impacts depending on data volumes.

Optimization Strategies

When working with large datasets, week extraction and processing can get slow. As a full-stack engineer you need to optimize:

• Leverage covering indexes on date columns used for grouping to speed up faceted aggregations.

• Generate week numbers in a materialized view for reuse instead of recalculating inline.

• Offload heavy processing to a summary table populated via scheduled batch workflow.

Here is an example summary table storing pre-calculated weekly metrics that is more efficient to query:

CREATE sales_weekly_summary
(
  week_id INT, --surrogate week id
  yr INT,  
  wk INT,  -- iso week   
  total_sales NUMERIC(12,2)   
)

ETL populates this via scheduled batch workflow for low-latency reads.

For ultra high-volume pipelines with billions of rows I recommend leveraging Big Data tools like Apache Spark and Presto to handle complex weekly aggregations.

Presto for example has powerful distributed functions to work with weeks:

date_trunc(‘week‘, event_timestamp) -> Truncates dates to week boundary
week(event_timestamp) -> Week of year 

Data Visualization

As a full-stack engineer, you often need to build graphical visualizations and dashboards off your weekly data:

Many business users think in weekly units. Use weekly tables to power clean, intuitive charts.

For granular date plotting, visualize week start/end boundaries or individual days. Highlight key metrics like the max weekly value.

Conclusion

We walked through various practical techniques to handle the nuances of preparing weekly data partitions in SQL – from basics to robust approaches.

As a full-stack engineer you‘ll find yourself regularly aggregating records by weeks for analysis and visualization in applications.

I hope this guide gives you new tools to gain mastery over weekly data processing! With these SQL learnings you can handle weekly reporting with ease.

Other periodizations like daily, monthly and quarterly introduce similar complexities. We may tackle those in a future article!