SQL и автоматизация текущей отчетности

2021/2022

Статус: Дисциплина общефакультетского пула

Кто читает: Международный институт экономики и финансов

Где читается: Международный институт экономики и финансов

Когда читается: 1, 2 модуль

Охват аудитории: для своего кампуса

Преподаватели: Шершуков Данила Вадимович

Язык: английский

Кредиты: 3

Контактные часы: 32

Full Syllabus

Abstract

Students are expected to be familiar with:•basic set operations (union, intersection, subtraction, cartesian product),•descriptive statistics,•Python (recommended) or ExcelDuring enrollment, a test may be held to check that students are familiar with basic set operations and basic descriptive statistics. The course develops practical and theoretical skills for using SQL in analytics and automating reports using BI systems. The course covers using SQL for extracting and analysing data and using SQL and Redash for report automation. The course consists of 7 classes and an exam. Each class consits of a lecture and a seminar. SQL is an important skill for analysts who work with large volumes of data and thus a competetive advantage for students during job search and at work. This course is designed to provide a lot of practice for students in writing queries, so that they could use SQL fluently. In this course, students will work with two SQL dialects: PostgreSQL and ClickHouse. PostgreSQL is an all-purpose DBMS, well-suited both for production (including highload) and analytics. ClickHouse is column-oriented and targets online analytical processing (OLAP). It excels at working with huge volumes of denormalized data, at the cost of lower flexibility and fewer features supported. While PostgreSQL is a perfect dialect to start learning SQL due to its systematic design, wide range of features and excelent documentation, ClickHouse is a good dialect to become aquainted with column-oriented databases and their use in data marts. The way reporting automation is done in a company greatly depends on the available infrastructure. The best case scenario is when a BI system is already deployed and analysts can focus on making reports. Without a BI system, creating infrastructure for running and viewing reports is required. It can be done in many ways, from Excel workbooks to creating simple BI systems with Python. In this course, we will assume a BI system (Redash) is available. Students who opt in for a challenge, will be provided with materials on doing similar reports and some basic infrastructure in Python.

Learning Objectives

To teach students how to use SQL for doing analytical tasks.
To improve students' skills in report automation

Expected Learning Outcomes

apply SQL to solve real-life analytical problems
become fluent in SELECT SQL queries
improve report automation skills by learning to automate reports in Excel or in Python, depending on their starting programming experience
use a database client with a GUI
use basic data definition and data manipulation SQL queries

Course Contents

Introduction to databases and SQL
Table expressions in detail
Select list and domain-specific functions
Window functions
Complex computing and ETLs
Working with geospatial data
SQL in interviews

Assessment Elements

home assignment 1
exam
home assignment 2
home assignment 3
home assignment 4
home assignment 5
home assignment 6
Project

Interim Assessment

2021/2022 2nd module
0.067 * home assignment 3 + 0.066 * home assignment 2 + 0.3 * Project + 0.3 * exam + 0.067 * home assignment 5 + 0.067 * home assignment 6 + 0.066 * home assignment 1 + 0.067 * home assignment 4

Bibliography

Recommended Core Bibliography

Beaulieu, A. (2009). Learning SQL : Master SQL Fundamentals: Vol. 2nd ed. O’Reilly Media.
Beighley, L. (2007). Head First SQL : Your Brain on SQL —— A Learner’s Guide. Reilly - O’Reilly Media.
Malik, U., Goldwasser, M., & Johnston, B. (2019). SQL for Data Analytics : Perform Fast and Efficient Data Analysis with the Power of SQL. Packt Publishing.

Recommended Additional Bibliography

Linoff, G. (2016). Data Analysis Using SQL and Excel: Vol. Second edition. Wiley.

Course Syllabus