Stratorys Onboarding

Appearance

Journey: Data fundamentals ​

After this journey you can: write SQL queries, understand table structure and data types, work with JSON and CSV, and explain the difference between row-oriented and columnar databases. You'll be ready to work with Stratorys data systems.

This journey does not cover: database administration, query optimization, data modeling theory, or specific ETL tools.

How to use this page: Each task gives you a goal and links to relevant documentation. Write your own query, run it, and compare the result with the expected output. Complete the tasks in order - some depend on tables or data created earlier.

Setup ​

Start PostgreSQL with Docker ​

bash
docker run -d --name pg-practice -e POSTGRES_PASSWORD=practice -e POSTGRES_DB=data_practice -p 5432:5432 postgres:16

Connect ​

bash
docker exec -it pg-practice psql -U postgres data_practice

Create tables and insert data ​

Run these statements inside psql to set up the practice dataset:

sql
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL,
    salary INTEGER NOT NULL,
    hire_date DATE NOT NULL
);

INSERT INTO employees (name, department, salary, hire_date) VALUES
    ('Alice', 'Engineering', 85000, '2022-03-15'),
    ('Bob', 'Engineering', 92000, '2021-06-01'),
    ('Carol', 'Data', 88000, '2022-09-20'),
    ('Dave', 'Data', 95000, '2020-01-10'),
    ('Eve', 'Engineering', 78000, '2023-02-28'),
    ('Frank', 'Operations', 72000, '2021-11-05'),
    ('Grace', 'Operations', 68000, '2023-07-15'),
    ('Henry', 'Data', 91000, '2022-04-01');

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    lead_id INTEGER REFERENCES employees(id),
    budget INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'active'
);

INSERT INTO projects (name, lead_id, budget, status) VALUES
    ('API Rewrite', 2, 150000, 'active'),
    ('Data Pipeline', 4, 200000, 'active'),
    ('Dashboard', 3, 80000, 'completed'),
    ('Infra Migration', NULL, 120000, 'planned');

CREATE TABLE timesheets (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(id),
    project_id INTEGER REFERENCES projects(id),
    hours DECIMAL(4,1) NOT NULL,
    work_date DATE NOT NULL
);

INSERT INTO timesheets (employee_id, project_id, hours, work_date) VALUES
    (1, 1, 8.0, '2024-01-15'),
    (1, 1, 7.5, '2024-01-16'),
    (2, 1, 8.0, '2024-01-15'),
    (2, 1, 8.0, '2024-01-16'),
    (2, 1, 6.0, '2024-01-17'),
    (3, 3, 8.0, '2024-01-15'),
    (3, 2, 4.0, '2024-01-16'),
    (4, 2, 8.0, '2024-01-15'),
    (4, 2, 8.0, '2024-01-16'),
    (4, 2, 8.0, '2024-01-17'),
    (5, 1, 6.0, '2024-01-15'),
    (5, 3, 4.0, '2024-01-16'),
    (6, 4, 8.0, '2024-01-15'),
    (8, 2, 7.0, '2024-01-15'),
    (8, 2, 8.0, '2024-01-16');

Verify with SELECT count(*) FROM employees; - you should get 8.

Task 1: Find high earners ​

Goal: Write a query that lists employees earning more than 85000, showing their name, department, and salary, sorted by salary from highest to lowest.

Docs:

Expected output:

 name  | department  | salary
-------+-------------+--------
 Dave  | Data        |  95000
 Bob   | Engineering |  92000
 Henry | Data        |  91000
 Carol | Data        |  88000
(4 rows)

Task 2: Headcount per department ​

Goal: Write a query that shows how many employees are in each department, sorted from largest department to smallest. For equal sizes, sort alphabetically by department name.

Docs:

Expected output:

 department  | headcount
-------------+-----------
 Data        |         3
 Engineering |         3
 Operations  |         2
(3 rows)

Task 3: Department salary report ​

Goal: Write a query showing the average salary per department, rounded to the nearest integer. Only include departments where the average exceeds 80000. Sort by average salary from highest to lowest.

INFO

PostgreSQL's AVG returns a decimal. Cast it to an integer with ::INTEGER to round it.

Docs:

Expected output:

 department  | avg_salary
-------------+------------
 Data        |      91333
 Engineering |      85000
(2 rows)

Task 4: Find project leads ​

Goal: Write a query that shows each project name alongside its lead's name. Only include projects that have a lead assigned.

Docs:

Expected output:

    project    | lead
---------------+-------
 API Rewrite   | Bob
 Data Pipeline | Dave
 Dashboard     | Carol
(3 rows)

Task 5: All projects with leads ​

Goal: Write a query that shows every project and its lead's name. Projects without a lead should show unassigned instead of an empty value.

INFO

COALESCE(value, fallback) returns the first non-null argument. Use it to replace NULL with a default.

Docs:

Expected output:

     project      |    lead
------------------+------------
 API Rewrite      | Bob
 Data Pipeline    | Dave
 Dashboard        | Carol
 Infra Migration  | unassigned
(4 rows)

Task 6: Above-average earners ​

Goal: Write a query that finds employees earning above the company average. Show their name, salary, and the company average as an integer. Sort by salary from highest to lowest.

Docs:

Expected output:

 name  | salary | company_avg
-------+--------+-------------
 Dave  |  95000 |       83625
 Bob   |  92000 |       83625
 Henry |  91000 |       83625
 Carol |  88000 |       83625
 Alice |  85000 |       83625
(5 rows)

Task 7: Busiest employees ​

Goal: Write a query that shows each employee's name and total hours logged across all projects. Only include employees who logged time. Sort by total hours from most to least.

INFO

This requires joining three tables. Think about which table connects employees to their hours.

Docs:

Expected output:

 name  | total_hours
-------+-------------
 Dave  |        24.0
 Bob   |        22.0
 Alice |        15.5
 Henry |        15.0
 Carol |        12.0
 Eve   |        10.0
 Frank |         8.0
(7 rows)

Task 8: Speed up a lookup ​

Goal: Create an index on the department column of the employees table. Then use EXPLAIN to show the query plan for filtering employees by department.

INFO

On small tables PostgreSQL may choose a sequential scan even with an index, because scanning 8 rows is faster than an index lookup. On tables with thousands of rows, the index makes a real difference.

Docs:

Expected output:

> \di employees*
                          List of relations
 Schema |          Name           | Type  | Owner | Table
--------+-------------------------+-------+-------+-----------
 public | employees_pkey          | index | ...   | employees
 public | idx_employees_dept      | index | ...   | employees
(2 rows)

Task 9: Store structured metadata ​

Material:

Add a JSONB column and populate it for some employees:

sql
ALTER TABLE employees ADD COLUMN metadata JSONB;
sql
UPDATE employees SET metadata = '{"team": "platform", "level": "senior"}' WHERE name = 'Alice';
sql
UPDATE employees SET metadata = '{"team": "platform", "level": "staff"}' WHERE name = 'Bob';
sql
UPDATE employees SET metadata = '{"team": "data-infra", "level": "senior"}' WHERE name = 'Carol';
sql
UPDATE employees SET metadata = '{"team": "data-infra", "level": "mid"}' WHERE name = 'Henry';

Goal: Write a query that finds all employees in the "platform" team by querying the JSONB metadata column. Show their name and team.

Docs:

Expected output:

 name  |   team
-------+----------
 Alice | platform
 Bob   | platform
(2 rows)

Task 10: Export to CSV ​

Goal: Export the employees table (id, name, department, salary) to a CSV file at /tmp/employees.csv with a header row.

INFO

Inside the Docker container you connect as the postgres superuser, so you can use the COPY command directly.

Docs:

Expected output:

> docker exec pg-practice head -5 /tmp/employees.csv
id,name,department,salary
1,Alice,Engineering,85000
2,Bob,Engineering,92000
3,Carol,Data,88000
4,Dave,Data,95000

Task 11: Choose the right data format ​

Three data formats come up constantly in data engineering:

FormatStructureBest for
CSVRows of comma-separated values, no nestingTabular data exchange, spreadsheet imports, simple exports
JSONKey-value pairs, supports nesting and arraysAPI responses, configuration, semi-structured data
ParquetColumnar binary format, compressed, typedLarge analytical datasets, data lakes, repeated queries over few columns

Goal: For each scenario, choose the best format.

#Scenario
1Export a report for another team to open in a spreadsheet
2Store webhook payloads with varying nested fields
3Archive 500GB of event data for quarterly analytics
4Application config with nested settings and comments
5Share a typed dataset between Python and Rust services

Docs:

Expected output:

1. CSV
2. JSON
3. Parquet
4. JSON
5. Parquet

Task 12: Row-oriented vs columnar storage ​

Two storage models to understand:

Row-oriented (PostgreSQL, MySQL) stores all columns of a row together on disk. Fast for reading or writing individual records.

Columnar (ClickHouse, DuckDB, Parquet files) stores all values of a single column together. Fast for scanning many rows but only a few columns. Compresses better because same-type values are stored together.

Goal: For each query, decide which storage model would be faster on a table with 10 million rows.

#Query
1Get one user by ID
2Compute the average salary across all employees
3Insert a new order
4Count events per day for the last year
5Update a user's email address
6Sum revenue by product category

Docs:

Expected output:

1. Row-oriented
2. Columnar
3. Row-oriented
4. Columnar
5. Row-oriented
6. Columnar

Cleanup ​

Inside psql:

sql
DROP TABLE timesheets;
sql
DROP TABLE projects;
sql
DROP TABLE employees;
sql
\q

Then stop and remove the container:

bash
docker stop pg-practice
bash
docker rm pg-practice

You now have the SQL fundamentals and understand data formats and storage models. For deeper PostgreSQL knowledge, work through the official tutorial. The real learning happens when you query Stratorys data - ask your team lead which schemas to explore first.