Join Dan Sullivan for an in-depth discussion in this video LAG and LEAD, part of Advanced SQL for Data Scientists.
- [Narrator] Newer versions of SQL…provide additional features for operating…on rows related to the currently processed row.…For example, if you want to know…a person's salary…and the next lower salary in the department,…we could use the lag function…to reference rows relative…to the currently processed rows.…So, let's look at an example.…We'll select,…department, last name, salary.…And now we'll introduce the lag function.…And I would like to lag on salary…over a partition…by department…and let's order that by…salary…and we'll use the descending keyword…and let's select this from…the Staff table.…
Now, it's best,…let's just execute this…and look at the results.…What we have is a result set that includes…the department name,…an employee's name,…their salary…and then a column labeled lag.…And what lag is referring to…is to the row that came before…the currently processed row.…So for example, the very first one…doesn't have a currently processed row…so the lag value is null.…But when we move to row two,…which is Automotive,…
The course begins with a brief overview of SQL. Then the five major topics a data scientist should understand when working with relational databases: basic statistics in SQL, data preparation in SQL, advanced filtering and data aggregation, window functions, and preparing data for use with analytics tools.
- Data manipulation
- ANSI standards
- SQL and variations
- Statistical functions in SQL
- String, numeric, and regular expression functions in SQL
- Advanced filtering techniques
- Advanced aggregation techniques
- Windowing functions for working with ordered data sets
Skill Level Advanced
SQL: Data Reporting and Analysiswith Emma Saunders2h 16m Intermediate
1. SQL as a Tool for Data Science
SQL data definition features5m 32s
2. Basic Statistics with SQL
3. Data Munging with SQL
4. Filtering, Joins, and Aggregation
5. Window Functions and Ordered Data
6. Preparing Data for Analytics Tools
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.