SQLite for analytics - course 3000 rub. from Stepik, training 40 hours, Date: December 2, 2023.
Miscellaneous / / December 05, 2023
Analysts, developers, and testers often work with data sets. Calculate sales statistics in different dimensions, convert and “clean” JSON of an inconvenient structure, find problems in a 500 MB nginx log - similar tasks have to be solved constantly.
To analyze or transform data, Excel or libraries like pandas are often used. These are not bad tools: Excel is good for the most basic tasks, and pandas is great once you dedicate the first 1000 hours to it.
But there is another tool. It is much more powerful than Excel, simpler and more expressive than pandas. This is SQLite - a full-fledged DBMS that does not require installation or configuration. SQLite takes SQL, a language specifically designed for data processing, and adds a bunch of nice features to it.
About what and for whom
The course will teach you how to use SQLite for everyday work with data:
Quickly analyze a large data set and find unobvious patterns.
Build a summary report from several sources, with groupings and analytical functions.
Load, convert and upload data in the required format.
It is convenient to work with JSON documents.
The course is for you if:
Are you tired of fiddling with Excel or don’t want to remember a thousand and one pandas commands.
Already know SQL and don't want to learn another language or library to solve everyday problems.
Do you want to master a universal data management tool once and for all?
You think that SQLite is a “toy” DBMS, not suitable for real tasks.
The course is not suitable if:
You don't know SQL at all.
Not ready to work with the command line.
The course is exclusively practical. We won't go into the five variations of FOREIGN KEY ON DELETE, transaction isolation levels, or the difference between normal forms. Instead, let's focus on specific tasks so you can immediately apply knowledge to your work.
You will be able to use SQLite in practice after the first module. And if you complete the course to the end, SQLite will turn into a universal tool that is suitable for almost any task.
The course is interactive. The exercises can be performed directly in the browser; there is no need to download or install anything. When you get the hang of it, repeat them in the console.
No programming skills required.
Content
SQLite Basics. We load the dataset, remember the SQL, and upload it to CSV.
Data cleaning. We “comb” the problematic data and understand the types.
Relationships in data. Join, join, and intersect tables in a data set.
Data → knowledge. We calculate the average, percentiles, distribution and correlation. We analyze the text.
JSON. We work natively with JSON documents and non-relational data structures.
Large sets. We work effectively with heavy tables.
Window functions. We master advanced analytical functions for cool reports.
How are classes conducted?
The course is assembled from modules, each module is a separate large topic: for example, “JSON” or “window functions”. The module consists of 5–6 lessons. The lesson consists of a lecture and several practical tasks.
Participants perform practical tasks in an interactive sandbox. Answers are checked automatically, so you don't have to wait. If something doesn’t work out, participants ask questions in the comments to the task, and I answer.
Lectures are written, no videos. I can explain complex things clearly, so it's a pleasure to read them. Lots of examples and pictures, no dry theory. At the end of each module there is a short summary.
Completing the module takes 2–4 hours, depending on your preparation. The entire course will take 15–30 hours.
Cost and participation
The course consists of seven modules and an exam. These are 47 lessons and 130 practical tasks. If you score 80% of points, you will receive a certificate, 90% of points will receive a certificate with honors.
The cost of the course is 3000 ₽. The first module is available for free.
All information published in this section of Lifehacker is not a public offer as defined by the provisions of Article 437 of the Civil Code of the Russian Federation, and is for informational purposes only.
The service uses technologies from the “Educated” project and does not provide educational services.