SQL Server has a robust set of tools to prepare, aggregate, and query time series data. This course will show you how to build and work with dates, parse dates from strings (and deal with invalid strings), and format dates for reporting. From there, you will see how SQL Server's built-in aggregation operators and window functions can solve important business problems like calculating running totals, finding moving averages, and displaying month-over-month differences using realistic sample data sets. You will also see how taking a different perspective on your data can solve difficult problems.
Working with Dates and TimesFree
This chapter covers date and time functionality in SQL Server, including building dates from component parts, formatting dates for reporting, and working with calendar tables.Building dates50 xpBreak out a date into year, month, and day100 xpBreak a date and time into component parts100 xpDate math and leap years100 xpRounding dates100 xpFormatting dates for reporting50 xpFormatting dates with CAST() and CONVERT()100 xpFormatting dates with FORMAT()100 xpWorking with calendar tables50 xpThe benefits of calendar tables50 xpTry out a calendar table100 xpJoining to a calendar table100 xp
Converting to Dates and Times
Here, we'll be converting strings and other inputs to date and time data types.Building dates from parts50 xpBuild dates from parts100 xpBuild dates and times from parts100 xpBuild dates and times with offsets from parts100 xpTranslating date strings50 xpCast strings to dates100 xpConvert strings to dates100 xpParse strings to dates100 xpWorking with offsets50 xpChanging a date's offset100 xpUsing the time zone DMV to look up times100 xpConverting to a date offset100 xpHandling invalid dates50 xpTry out type-safe date functions100 xpConvert imported data to dates with time zones100 xpTest type-safe conversion function performance100 xp
Aggregating Time Series Data
In this chapter, we will learn techniques to aggregate data over time. We will briefly review aggregation functions and statistical aggregation functions. We will cover upsampling and downsampling of data. Finally, we will look at the grouping operators.Basic aggregate functions50 xpSummarize data over a time frame100 xpCalculating distinct counts100 xpCalculating filtered aggregates100 xpStatistical aggregate functions50 xpWorking with statistical aggregate functions100 xpCalculating median in SQL Server100 xpDownsampling and upsampling data50 xpDownsample to a daily grain100 xpDownsample to a weekly grain100 xpDownsample using a calendar table100 xpGrouping by ROLLUP, CUBE, and GROUPING SETS50 xpGenerate a summary with ROLLUP100 xpView all aggregations with CUBE100 xpGenerate custom groupings with GROUPING SETS100 xpCombine multiple aggregations in one query100 xp
Answering Time Series Questions with Window Functions
In this chapter, we will learn how to use window functions to perform calculations over time, including calculating running totals and moving averages, calculating intervals, and finding the maximum levels of overlap.Using aggregation functions over windows50 xpContrasting ROW_NUMBER(), RANK(), and DENSE_RANK()100 xpAggregate window functions100 xpCalculating running totals and moving averages50 xpRunning totals with SUM()100 xpInvestigating window frames100 xpCalculating moving averages100 xpWorking with LAG() and LEAD()50 xpSeeing prior and future periods100 xpSeeing the prior three periods100 xpCalculating days elapsed between incidents100 xpFinding maximum levels of overlap50 xpAnalyze client data for potential fraud100 xpBuild a stream of events100 xpComplete the fraud analysis100 xpWrapping up50 xp
PrerequisitesIntermediate SQL Server
Maham KhanSee More
Senior Data Scientist, YouView TV
Maham is a Data Scientist on a mission to make data skills accessible for everyone. She's worked on creating toolkits and exploring experimental applications of data science for urban analytics, disaster risk management, and climate change mitigation at the World Bank. She has a background in Experimental Psychology and Philosophy from the University of Oxford and Urban Data Science from NYU.