SQL + Python

Quizz #1 SQL.md

Estimate: 4h

SPECIFICATION

Create 2 sql schemas for PostgreSQL and Amazon Redshift based on models below.

DB entities and relations

supplier (1-many) act_of_delivery (1-many) shipment_log (many-1) item

supplier
id
name

act_of_delivery
id
warehouse_id
supplier_id
datetime_utc

package
act_id
item_id
count

item
id
title
cost
height
length
depth
weight
is_vertical_stackable

Optimize tables for the queries below and leave description what and why you used.

Queries

• warehouse_id, supplier_id, sum(cost * count), sum(count), sum(cost), day(datetime_utc)

• warehouse_id, supplier_id, sum(cost * count), sum(count), sum(cost), month(datetime_utc)

• warehouse_id, supplier_id, sum(cost * count), sum(count), sum(cost), year(datetime_utc)

• warehouse_id, sum(count), sum(cost), day(datetime_utc), order by day

• warehouse_id, sum(count), sum(cost), month(datetime_utc), order by month

• warehouse_id, sum(count), sum(cost), year(datetime_utc), order by year

• warehouse_id, sum(height * length * depth), count(act_id)

• item_id, supplier_id, sum(cost * count), sum(count), sum(cost)

Quizz #2 Python.md

Estimate: 8h

SPECIFICATIONS

There is some storage space and its problem is that the warehouse and delivery area have different databases and servers. We need to process the receipt report with ETL pipeline after every shipment.

The task is to design a database access for the warehouse from Quizz #1 and service for the operation and monitoring of the warehouse workspace and product residences.

Create ORM models above Quizz #1 schema and provide CRUD intefraces via Flask.

DEPENDENCIES

Mandatory:

• MacOS/Linux
• PostgreSQL 9+
• Python 3+
• Flask
• SQLAlchemy, alembic, triggers
• makefile // to run and test
• docker

Optional (but very good to have):

• docker-compose
• pylint