Analytical SQL with DuckDB
With server-free DuckDB you query CSV, JSON and Parquet files directly and analyse logs with window functions, CTEs and time bucketing.
Level 201 · 19 lessons · 18 practice labs
Lessons
Getting started
- What an analytical database is and why DuckDB
- The DuckDB command line and a first query
- Querying files directly: CSV, JSON, Parquet
- Schema inference and data types
- Quick task: query an access log with SQL
Analytical queries
- GROUP BY, HAVING and conditional counts
- Time bucketing: hourly and daily summaries
- Breaking queries into steps with CTEs
- Window functions: rank, lag, moving average
- Enriching with joins
- Quick task: first and last seen time per IP
Advanced techniques
- Nested JSON and UNNEST
- Text and regular expressions in SQL
- PIVOT and UNPIVOT
- Writing results as partitioned Parquet
- Using DuckDB from Python
- Quick task: find regular-interval connections with SQL
Performance and report
- EXPLAIN and query performance
- Capstone: analysing a week of web attacks
Practice labs
- Query a Parquet file directly: Query a Parquet event file with DuckDB without importing it and find the five hosts producing the most events.
- Read many files with a glob: Read hundreds of per-day CSV files in one query, add the file name as a column and produce the row count per day.
- Fix wrong type inference: Declare column types explicitly for a file where DuckDB guesses wrongly and move the misread rows into a separate table.
- A status summary with conditional counts: Compute total, successful and failed requests per endpoint in one query using FILTER clauses.
- Five-minute time buckets: Split e-mail sends into five-minute buckets with time_bucket and use HAVING to keep only unusually high buckets.
- Step-by-step analysis with CTEs: Break a monolithic unreadable query into CTE steps, check each intermediate result and prove it gives the same output.
- First and last login per user: Use window functions to compute each user's first and last login and the number of days between them.
- Gaps between sessions with LAG: Use LAG to compute the time between consecutive connections and find source-destination pairs with an almost constant interval.
- Deviation from a moving average: Compute a seven-day moving average per host and list, in order, the days when traffic exceeded three times that average.
- Open a JSON list with UNNEST: Open alert records with a tag list using UNNEST and produce the alert count per tag.
- Split URLs with regular expressions: Use regexp_extract to split request URLs into domain, path and query parameters and find the endpoint carrying the most parameters.
- A day-hour matrix with PIVOT: Produce a branch×hour event matrix with PIVOT, turn it back with UNPIVOT and show the total is preserved.
- Write as partitioned Parquet: Export a query result as day-partitioned Parquet and record the resulting folder layout and file count.
- DuckDB and DataFrames from Python: Build a Python script that queries a pandas DataFrame with DuckDB, gets the result back as a DataFrame and writes it to CSV.
- Speed up a slow query with EXPLAIN: Find a slow query's bottleneck in EXPLAIN ANALYZE output and rewrite it to cut the run time at least in half.
- Join events to the asset list: Join the alert table with the asset CSV and add owner and location to every alert.
- From IP to network with a range join: Match source IPs to the address ranges in a network-zone table and produce blocked connections per zone.
- Match to the nearest record in time: Use ASOF JOIN to match each network event to the DHCP lease in force and find which device held the IP at that time.
About this course
With server-free DuckDB you query CSV, JSON and Parquet files directly and analyse logs with window functions, CTEs and time bucketing.
What you will learn
- Getting started
- Analytical queries
- Advanced techniques
- Performance and report
Prerequisites
Finishing the level 101 courses of the same path, or knowing those topics, is enough.
Lab environment
Lessons run in your own Kali Linux terminal that opens in the browser. Nothing to install; the lab starts clean in every lesson.
Frequently asked questions
- Is this course right for my level?
- The course is at level 201; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.