===== lab04 - Wed Nov  4 12:47:42 PM UTC 2026 =====
$ cd /home/onyxia/work/ece-2026-bigdata
$ echo 'https://s3.seaweedfs.adm.adaltas.cloud user-m-anane-ece'
https://s3.seaweedfs.adm.adaltas.cloud user-m-anane-ece
$ aws s3 --profile default ls s3://user-m-anane-ece/bronze/
2026-11-04 12:47:31     331568 orders.csv
2026-11-04 12:47:25       7351 users.csv
$ command -v duckdb
/usr/local/bin/duckdb
$ duckdb --version
-- Loading resources from /home/onyxia/.duckdbrc
v1.5.5 (Variegata) d8cdaa33fd
$ duckdb -c 'SELECT '\''Hello DuckDB'\'' AS greeting, 21 * 2 AS answer;'
-- Loading resources from /home/onyxia/.duckdbrc
┌──────────────┬────────┐
│   greeting   │ answer │
│   varchar    │ int32  │
├──────────────┼────────┤
│ Hello DuckDB │     42 │
└──────────────┴────────┘
$ duckdb -c 'SELECT name, type, provider, persistent, storage FROM duckdb_secrets();'
-- Loading resources from /home/onyxia/.duckdbrc
┌──────────────────────┬─────────┬──────────┬────────────┬────────────┐
│         name         │  type   │ provider │ persistent │  storage   │
│       varchar        │ varchar │ varchar  │  boolean   │  varchar   │
├──────────────────────┼─────────┼──────────┼────────────┼────────────┤
│ s3_onyxia_connection │ s3      │ config   │ true       │ local_file │
└──────────────────────┴─────────┴──────────┴────────────┴────────────┘
$ duckdb -c 'SELECT name, regexp_replace(secret_string, '\''(key_id=)[^;]+'\'', '\''\1***'\'') AS secret_string FROM duckdb_secrets();'
$ grep -oE 'endpoint=[^;]*|url_style=[^;]*'
$ sort -u
-- Loading resources from /home/onyxia/.duckdbrc
endpoint=s3.seaweedfs.adm.adaltas.cloud
url_style=path │
$ cat
$ cat init.sql
SET VARIABLE bucket = 's3://user-m-anane-ece';
SET TimeZone = 'UTC';
$ printf '*.duckdb\ninit.sql\n'
$ cat
$ duckdb -echo analytics.duckdb -init init.sql
-- Loading resources from init.sql
SELECT getvariable('bucket') AS bucket;
┌───────────────────────┐
│        bucket         │
│        varchar        │
├───────────────────────┤
│ s3://user-m-anane-ece │
└───────────────────────┘
-- Bronze
SELECT uuid, username, name, birthdate FROM read_csv(getvariable('bucket') || '/bronze/users.csv', header = true, strict_mode = false) LIMIT 3;
┌──────────────────────────────────────┬────────────────┬────────────────┬────────────┐
│                 uuid                 │    username    │      name      │ birthdate  │
│               varchar                │    varchar     │    varchar     │    date    │
├──────────────────────────────────────┼────────────────┼────────────────┼────────────┤
│ bdd640fb-0667-4ad1-9c80-317fa3b1799d │ garzaanthony   │ Charles Garcia │ 1935-10-26 │
│ 17be3111-1a2a-43ed-962b-0f79c37459ee │ blairamanda    │ Ryan Munoz     │ 2010-01-21 │
│ 47294739-614f-43d7-99db-3ad0ddd1dfb2 │ elizabethmiles │ Jacob Wood     │ 1941-11-24 │
└──────────────────────────────────────┴────────────────┴────────────────┴────────────┘
SELECT Delimiter, Quote, HasHeader FROM sniff_csv(getvariable('bucket') || '/bronze/users.csv');
┌───────────┬─────────┬───────────┐
│ Delimiter │  Quote  │ HasHeader │
│  varchar  │ varchar │  boolean  │
├───────────┼─────────┼───────────┤
│ ,         │ "       │ true      │
└───────────┴─────────┴───────────┘
DESCRIBE FROM read_csv(getvariable('bucket') || '/bronze/orders.csv');
┌────────────────────────────────────┐
│              Describe              │
│                                    │
│ uuid      varchar                  │
│ user_uuid varchar                  │
│ date      timestamp with time zone │
│ quantity  bigint                   │
│ product   varchar                  │
└────────────────────────────────────┘
SELECT column_name, column_type, approx_unique, null_percentage FROM (SUMMARIZE FROM read_csv(getvariable('bucket') || '/bronze/orders.csv'));
┌─────────────┬──────────────────────────┬───────────────┬─────────────────┐
│ column_name │       column_type        │ approx_unique │ null_percentage │
│   varchar   │         varchar          │     int64     │  decimal(9,2)   │
├─────────────┼──────────────────────────┼───────────────┼─────────────────┤
│ uuid        │ VARCHAR                  │          3167 │            0.00 │
│ user_uuid   │ VARCHAR                  │            40 │            0.00 │
│ date        │ TIMESTAMP WITH TIME ZONE │          3566 │            0.00 │
│ quantity    │ BIGINT                   │             5 │            0.00 │
│ product     │ VARCHAR                  │             6 │            0.00 │
└─────────────┴──────────────────────────┴───────────────┴─────────────────┘
-- Chargement
CREATE OR REPLACE TABLE users AS FROM read_csv(getvariable('bucket') || '/bronze/users.csv');
CREATE OR REPLACE TABLE orders AS FROM read_csv(getvariable('bucket') || '/bronze/orders.csv');
.tables
 ─────────────────────── analytics ─────────────────────── 
 ───────────────────────── main ────────────────────────── 
┌───────────────────┐┌────────────────────────────────────┐
│       users       ││               orders               │
│                   ││                                    │
│ uuid      varchar ││ uuid      varchar                  │
│ username  varchar ││ user_uuid varchar                  │
│ name      varchar ││ date      timestamp with time zone │
│ sex       varchar ││ quantity  bigint                   │
│ address   varchar ││ product   varchar                  │
│ mail      varchar ││                                    │
│ birthdate date    ││             2829 rows              │
│                   │└────────────────────────────────────┘
│      50 rows      │
└───────────────────┘
SELECT (SELECT count(*) FROM users) AS users, (SELECT count(*) FROM orders) AS orders;
┌───────┬────────┐
│ users │ orders │
│ int64 │ int64  │
├───────┼────────┤
│    50 │   2829 │
└───────┴────────┘
SELECT count(DISTINCT uuid) AS distinct_orders, count(DISTINCT user_uuid) AS distinct_users FROM orders;
┌─────────────────┬────────────────┐
│ distinct_orders │ distinct_users │
│      int64      │     int64      │
├─────────────────┼────────────────┤
│            2829 │             50 │
└─────────────────┴────────────────┘
SELECT min(date) AS first_order, max(date) AS last_order FROM orders;
┌───────────────────────────────┬───────────────────────────────┐
│          first_order          │          last_order           │
│   timestamp with time zone    │   timestamp with time zone    │
├───────────────────────────────┼───────────────────────────────┤
│ 2020-01-01 00:02:17.891488+00 │ 2020-04-27 20:12:06.167622+00 │
└───────────────────────────────┴───────────────────────────────┘
-- Qualite
SELECT count(*) AS orphan_orders FROM orders o ANTI JOIN users u ON o.user_uuid = u.uuid;
┌───────────────┐
│ orphan_orders │
│     int64     │
├───────────────┤
│             0 │
└───────────────┘
SELECT count(*) AS inactive_users FROM users u ANTI JOIN orders o ON o.user_uuid = u.uuid;
┌────────────────┐
│ inactive_users │
│     int64      │
├────────────────┤
│              0 │
└────────────────┘
SELECT uuid, count(*) AS occurrences FROM orders GROUP BY uuid HAVING count(*) > 1;
┌─────────┬─────────────┐
│  uuid   │ occurrences │
│ varchar │    int64    │
└─────────┴─────────────┘
         0 rows        
-- Agregations
SELECT product, count(*) AS orders, sum(quantity) AS quantity, round(100 * sum(quantity) / sum(sum(quantity)) OVER (), 1) AS share_pct FROM orders GROUP BY product ORDER BY quantity DESC;
┌───────────┬────────┬──────────┬───────────┐
│  product  │ orders │ quantity │ share_pct │
│  varchar  │ int64  │  int128  │  double   │
├───────────┼────────┼──────────┼───────────┤
│ croissant │    491 │     1523 │      17.7 │
│ drink     │    484 │     1493 │      17.3 │
│ brioche   │    468 │     1424 │      16.5 │
│ bread     │    458 │     1420 │      16.5 │
│ donut     │    462 │     1390 │      16.1 │
│ cookie    │    466 │     1365 │      15.8 │
└───────────┴────────┴──────────┴───────────┘
SELECT date_trunc('month', date) AS month, count(*) AS orders, sum(quantity) AS quantity FROM orders GROUP BY month ORDER BY month;
┌──────────────────────────┬────────┬──────────┐
│          month           │ orders │ quantity │
│ timestamp with time zone │ int64  │  int128  │
├──────────────────────────┼────────┼──────────┤
│ 2020-01-01 00:00:00+00   │    744 │     2297 │
│ 2020-02-01 00:00:00+00   │    696 │     2154 │
│ 2020-03-01 00:00:00+00   │    744 │     2192 │
│ 2020-04-01 00:00:00+00   │    645 │     1972 │
└──────────────────────────┴────────┴──────────┘
-- Jointures
SELECT u.username, u.name, count(*) AS orders, sum(o.quantity) AS quantity FROM orders o JOIN users u ON o.user_uuid = u.uuid GROUP BY ALL ORDER BY quantity DESC LIMIT 5;
┌─────────────┬────────────────────┬────────┬──────────┐
│  username   │        name        │ orders │ quantity │
│   varchar   │      varchar       │ int64  │  int128  │
├─────────────┼────────────────────┼────────┼──────────┤
│ elizabeth57 │ Connie Holt        │     97 │      317 │
│ karenhudson │ Angelica Keith     │     97 │      303 │
│ jsmith      │ Shane Alexander    │    100 │      301 │
│ hoganashlee │ Patricia Jefferson │    100 │      289 │
│ wrightjames │ Monica Mcbride     │     92 │      285 │
└─────────────┴────────────────────┴────────┴──────────┘
SELECT (date_diff('year', u.birthdate, DATE '2020-01-01') // 20) * 20 AS age_group, count(DISTINCT u.uuid) AS users, count(*) AS orders, round(avg(o.quantity), 2) AS avg_quantity FROM orders o JOIN users u ON o.user_uuid = u.uuid GROUP BY age_group ORDER BY age_group;
┌───────────┬───────┬────────┬──────────────┐
│ age_group │ users │ orders │ avg_quantity │
│   int64   │ int64 │ int64  │    double    │
├───────────┼───────┼────────┼──────────────┤
│         0 │    14 │    686 │         3.08 │
│        20 │     7 │    483 │         3.02 │
│        40 │    12 │    721 │         3.02 │
│        60 │    11 │    694 │         3.02 │
│        80 │     5 │    198 │         3.14 │
│       100 │     1 │     47 │         3.19 │
└───────────┴───────┴────────┴──────────────┘
-- Fenetres
WITH daily AS (SELECT date::DATE AS day, sum(quantity) AS quantity FROM orders GROUP BY day)
SELECT day, quantity, sum(quantity) OVER (ORDER BY day) AS cumulative, round(avg(quantity) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1) AS avg_7d FROM daily ORDER BY day LIMIT 10;
┌────────────┬──────────┬────────────┬────────┐
│    day     │ quantity │ cumulative │ avg_7d │
│    date    │  int128  │   int128   │ double │
├────────────┼──────────┼────────────┼────────┤
│ 2020-01-01 │       86 │         86 │   86.0 │
│ 2020-01-02 │       62 │        148 │   74.0 │
│ 2020-01-03 │       80 │        228 │   76.0 │
│ 2020-01-04 │       66 │        294 │   73.5 │
│ 2020-01-05 │       72 │        366 │   73.2 │
│ 2020-01-06 │       75 │        441 │   73.5 │
│ 2020-01-07 │       73 │        514 │   73.4 │
│ 2020-01-08 │       81 │        595 │   72.7 │
│ 2020-01-09 │       89 │        684 │   76.6 │
│ 2020-01-10 │       75 │        759 │   75.9 │
└────────────┴──────────┴────────────┴────────┘
  10 rows                           4 columns
SELECT strftime(date, '%Y-%m') AS month, product, sum(quantity) AS quantity FROM orders GROUP BY month, product QUALIFY rank() OVER (PARTITION BY month ORDER BY sum(quantity) DESC) = 1 ORDER BY month;
┌─────────┬───────────┬──────────┐
│  month  │  product  │ quantity │
│ varchar │  varchar  │  int128  │
├─────────┼───────────┼──────────┤
│ 2020-01 │ donut     │      438 │
│ 2020-02 │ croissant │      392 │
│ 2020-03 │ bread     │      411 │
│ 2020-04 │ croissant │      386 │
└─────────┴───────────┴──────────┘
-- Pivot
PIVOT (SELECT strftime(date, '%Y-%m') AS month, product, quantity FROM orders) ON product USING sum(quantity) ORDER BY month;
PIVOT (SELECT strftime(date, '%Y-%m') AS month, product, quantity FROM orders) ON product USING sum(quantity) ORDER BY month
┌─────────┬────────┬─────────┬────────┬───────────┬────────┬────────┐
│  month  │ bread  │ brioche │ cookie │ croissant │ donut  │ drink  │
│ varchar │ int128 │ int128  │ int128 │  int128   │ int128 │ int128 │
├─────────┼────────┼─────────┼────────┼───────────┼────────┼────────┤
│ 2020-01 │    315 │     431 │    376 │       360 │    438 │    377 │
│ 2020-02 │    362 │     350 │    371 │       392 │    314 │    365 │
│ 2020-03 │    411 │     333 │    340 │       385 │    321 │    402 │
│ 2020-04 │    332 │     310 │    278 │       386 │    317 │    349 │
└─────────┴────────┴─────────┴────────┴───────────┴────────┴────────┘
-- ===== Exercices =====
-- 1. Nombre moyen de commandes par utilisateur et quantite moyenne par commande
SELECT round(count(*) / count(DISTINCT user_uuid), 2) AS avg_orders_per_user, round(avg(quantity), 2) AS avg_quantity_per_order FROM orders;
┌─────────────────────┬────────────────────────┐
│ avg_orders_per_user │ avg_quantity_per_order │
│       double        │         double         │
├─────────────────────┼────────────────────────┤
│               56.58 │                   3.05 │
└─────────────────────┴────────────────────────┘
-- 2. Premiere / derniere commande et nombre de jours entre les deux, par utilisateur
SELECT u.username, min(o.date) AS first_order, max(o.date) AS last_order, date_diff('day', min(o.date), max(o.date)) AS days_between FROM orders o JOIN users u ON o.user_uuid = u.uuid GROUP BY u.username ORDER BY first_order LIMIT 10;
┌────────────────┬───────────────────────────────┬───────────────────────────────┬──────────────┐
│    username    │          first_order          │          last_order           │ days_between │
│    varchar     │   timestamp with time zone    │   timestamp with time zone    │    int64     │
├────────────────┼───────────────────────────────┼───────────────────────────────┼──────────────┤
│ garzaanthony   │ 2020-01-01 00:02:17.891488+00 │ 2020-01-04 10:28:17.77967+00  │            3 │
│ blairamanda    │ 2020-01-04 11:59:53.242326+00 │ 2020-01-05 03:02:14.273688+00 │            1 │
│ elizabethmiles │ 2020-01-05 04:50:10.331513+00 │ 2020-01-06 01:03:27.852411+00 │            1 │
│ ithomas        │ 2020-01-06 02:48:59.231613+00 │ 2020-01-07 16:59:25.164747+00 │            1 │
│ kayla51        │ 2020-01-07 17:49:04.785279+00 │ 2020-01-07 20:31:55.964946+00 │            0 │
│ millertodd     │ 2020-01-07 21:58:38.925654+00 │ 2020-01-07 22:48:22.203716+00 │            0 │
│ perezrebecca   │ 2020-01-07 23:37:11.211033+00 │ 2020-01-08 10:45:20.193352+00 │            1 │
│ wrightjames    │ 2020-01-08 11:20:45.094193+00 │ 2020-01-12 06:23:02.514874+00 │            4 │
│ smoore         │ 2020-01-12 07:14:53.401298+00 │ 2020-01-12 23:09:49.797471+00 │            0 │
│ sarah35        │ 2020-01-13 00:10:01.066635+00 │ 2020-01-15 17:53:07.152912+00 │            2 │
└────────────────┴───────────────────────────────┴───────────────────────────────┴──────────────┘
  10 rows                                                                             4 columns
-- 3. Heure de la journee avec la plus grande quantite vendue
SELECT hour(date) AS hour_of_day, sum(quantity) AS quantity FROM orders GROUP BY hour_of_day ORDER BY quantity DESC LIMIT 3;
┌─────────────┬──────────┐
│ hour_of_day │ quantity │
│    int64    │  int128  │
├─────────────┼──────────┤
│           6 │      385 │
│          15 │      382 │
│           0 │      379 │
└─────────────┴──────────┘
-- 4. Variation mensuelle (%) de la quantite par produit, avec lag
WITH monthly AS (SELECT product, strftime(date, '%Y-%m') AS month, sum(quantity) AS quantity FROM orders GROUP BY ALL)
SELECT product, month, quantity, lag(quantity) OVER (PARTITION BY product ORDER BY month) AS previous, round(100.0 * (quantity - lag(quantity) OVER (PARTITION BY product ORDER BY month)) / lag(quantity) OVER (PARTITION BY product ORDER BY month), 1) AS variation_pct FROM monthly ORDER BY product, month;
┌───────────┬─────────┬──────────┬──────────┬───────────────┐
│  product  │  month  │ quantity │ previous │ variation_pct │
│  varchar  │ varchar │  int128  │  int128  │    double     │
├───────────┼─────────┼──────────┼──────────┼───────────────┤
│ bread     │ 2020-01 │      315 │     NULL │          NULL │
│ bread     │ 2020-02 │      362 │      315 │          14.9 │
│ bread     │ 2020-03 │      411 │      362 │          13.5 │
│ bread     │ 2020-04 │      332 │      411 │         -19.2 │
│ brioche   │ 2020-01 │      431 │     NULL │          NULL │
│ brioche   │ 2020-02 │      350 │      431 │         -18.8 │
│ brioche   │ 2020-03 │      333 │      350 │          -4.9 │
│ brioche   │ 2020-04 │      310 │      333 │          -6.9 │
│ cookie    │ 2020-01 │      376 │     NULL │          NULL │
│ cookie    │ 2020-02 │      371 │      376 │          -1.3 │
│ cookie    │ 2020-03 │      340 │      371 │          -8.4 │
│ cookie    │ 2020-04 │      278 │      340 │         -18.2 │
│ croissant │ 2020-01 │      360 │     NULL │          NULL │
│ croissant │ 2020-02 │      392 │      360 │           8.9 │
│ croissant │ 2020-03 │      385 │      392 │          -1.8 │
│ croissant │ 2020-04 │      386 │      385 │           0.3 │
│ donut     │ 2020-01 │      438 │     NULL │          NULL │
│ donut     │ 2020-02 │      314 │      438 │         -28.3 │
│ donut     │ 2020-03 │      321 │      314 │           2.2 │
│ donut     │ 2020-04 │      317 │      321 │          -1.2 │
│ drink     │ 2020-01 │      377 │     NULL │          NULL │
│ drink     │ 2020-02 │      365 │      377 │          -3.2 │
│ drink     │ 2020-03 │      402 │      365 │          10.1 │
│ drink     │ 2020-04 │      349 │      402 │         -13.2 │
└───────────┴─────────┴──────────┴──────────┴───────────────┘
  24 rows                                         5 columns
-- 5. Utilisateurs ayant commande chaque produit au moins une fois
SELECT u.username, count(DISTINCT o.product) AS products FROM orders o JOIN users u ON o.user_uuid = u.uuid GROUP BY u.username HAVING count(DISTINCT o.product) = (SELECT count(DISTINCT product) FROM orders) ORDER BY u.username;
┌──────────────────┬──────────┐
│     username     │ products │
│     varchar      │  int64   │
├──────────────────┼──────────┤
│ ahancock         │        6 │
│ atkinsbarbara    │        6 │
│ blairamanda      │        6 │
│ bruce43          │        6 │
│ calebrussell     │        6 │
│ cgrimes          │        6 │
│ charleslandry    │        6 │
│ cstone           │        6 │
│ danamullins      │        6 │
│ danderson        │        6 │
│ dayderek         │        6 │
│ debra12          │        6 │
│ elizabeth57      │        6 │
│ elizabeth65      │        6 │
│ elizabethmiles   │        6 │
│ farrelldebra     │        6 │
│ garzaanthony     │        6 │
│ gilbertwilliam   │        6 │
│ hhoffman         │        6 │
│ hoganashlee      │        6 │
│      ·           │        · │
│      ·           │        · │
│      ·           │        · │
│ karenhudson      │        6 │
│ lozanojulie      │        6 │
│ lucasrodney      │        6 │
│ mariahdavis      │        6 │
│ mccormickdeborah │        6 │
│ mcmillandennis   │        6 │
│ michael22        │        6 │
│ nicolejohnson    │        6 │
│ pvalentine       │        6 │
│ robin74          │        6 │
│ ronaldstephens   │        6 │
│ sarah35          │        6 │
│ smoore           │        6 │
│ snguyen          │        6 │
│ stephen00        │        6 │
│ trose            │        6 │
│ vanessafernandez │        6 │
│ wilsontara       │        6 │
│ wrightjames      │        6 │
│ yobrien          │        6 │
└──────────────────┴──────────┘
  45 rows (40 shown)2 columns
SELECT count(*) AS users_with_all_products FROM (SELECT user_uuid FROM orders GROUP BY user_uuid HAVING count(DISTINCT product) = 6);
┌─────────────────────────┐
│ users_with_all_products │
│          int64          │
├─────────────────────────┤
│                      45 │
└─────────────────────────┘
-- Parquet
COPY orders TO (getvariable('bucket') || '/analytics/orders.parquet') (FORMAT parquet);
SELECT path_in_schema, type, compression, total_compressed_size, total_uncompressed_size FROM parquet_metadata(getvariable('bucket') || '/analytics/orders.parquet');
┌────────────────┬────────────┬─────────────┬───────────────────────┬─────────────────────────┐
│ path_in_schema │    type    │ compression │ total_compressed_size │ total_uncompressed_size │
│    varchar     │  varchar   │   varchar   │         int64         │          int64          │
├────────────────┼────────────┼─────────────┼───────────────────────┼─────────────────────────┤
│ uuid           │ BYTE_ARRAY │ SNAPPY      │                102191 │                  113189 │
│ user_uuid      │ BYTE_ARRAY │ SNAPPY      │                  2125 │                    2348 │
│ date           │ INT64      │ SNAPPY      │                 22667 │                   22661 │
│ quantity       │ INT64      │ SNAPPY      │                  1242 │                    1245 │
│ product        │ BYTE_ARRAY │ SNAPPY      │                  1271 │                    1266 │
└────────────────┴────────────┴─────────────┴───────────────────────┴─────────────────────────┘
COPY orders TO (getvariable('bucket') || '/analytics/orders_by_product') (FORMAT parquet, PARTITION_BY (product));
SELECT file FROM glob(getvariable('bucket') || '/analytics/orders_by_product/**');
┌────────────────────────────────────────────────────────────────────────────────────┐
│                                        file                                        │
│                                      varchar                                       │
├────────────────────────────────────────────────────────────────────────────────────┤
│ s3://user-m-anane-ece/analytics/orders_by_product/product=bread/data_0.parquet     │
│ s3://user-m-anane-ece/analytics/orders_by_product/product=brioche/data_0.parquet   │
│ s3://user-m-anane-ece/analytics/orders_by_product/product=cookie/data_0.parquet    │
│ s3://user-m-anane-ece/analytics/orders_by_product/product=croissant/data_0.parquet │
│ s3://user-m-anane-ece/analytics/orders_by_product/product=donut/data_0.parquet     │
│ s3://user-m-anane-ece/analytics/orders_by_product/product=drink/data_0.parquet     │
└────────────────────────────────────────────────────────────────────────────────────┘
EXPLAIN ANALYZE SELECT count(*) FROM read_parquet(getvariable('bucket') || '/analytics/orders_by_product/*/*.parquet') WHERE product = 'cookie';
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYZE SELECT count(*) FROM read_parquet(getvariable('bucket') || '/analytics/orders_by_product/*/*.parquet') WHERE product = 'cookie';
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││         HTTPFS HTTP Stats         ││
││                                   ││
││            in: 33.7 KiB           ││
││            out: 0 bytes           ││
││              #HEAD: 0             ││
││              #GET: 3              ││
││              #PUT: 0              ││
││              #POST: 0             ││
││             #DELETE: 0            ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 0.142s              ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│                           │
│           0 rows          │
│           0.00s           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│        count_star()       │
│                           │
│                           │
│                           │
│           1 row           │
│           0.00s           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         TABLE_SCAN        │
│    ────────────────────   │
│         Function:         │
│        READ_PARQUET       │
│                           │
│       File Filters:       │
│    (product = 'cookie')   │
│                           │
│    Scanning Files: 1/6    │
│    Total Files Read: 1    │
│                           │
│        Filename(s):       │
│   s3://user-m-anane-ece   │
│         /analytics        │
│ /orders_by_product/product│
│   =cookie/data_0.parquet  │
│                           │
│                           │
│                           │
│          466 rows         │
│           0.00s           │
└───────────────────────────┘
$ aws s3 --profile default ls s3://user-m-anane-ece/analytics/ --recursive
2026-11-04 12:47:45     130526 analytics/orders.parquet
2026-11-04 12:47:46      23967 analytics/orders_by_product/product=bread/data_0.parquet
2026-11-04 12:47:46      24265 analytics/orders_by_product/product=brioche/data_0.parquet
2026-11-04 12:47:46      24282 analytics/orders_by_product/product=cookie/data_0.parquet
2026-11-04 12:47:46      25322 analytics/orders_by_product/product=croissant/data_0.parquet
2026-11-04 12:47:46      24181 analytics/orders_by_product/product=donut/data_0.parquet
2026-11-04 12:47:46      24949 analytics/orders_by_product/product=drink/data_0.parquet
$ uv run dataset-orders -u 5000 -o csv
$ ls -lh orders_large.csv
-rw-r--r-- 1 onyxia users 29M Nov  4 12:48 orders_large.csv
$ s5cmd --profile default cp orders_large.csv s3://user-m-anane-ece/large/orders.csv
cp orders_large.csv s3://user-m-anane-ece/large/orders.csv
$ cat
$ duckdb -echo analytics.duckdb -init init.sql
-- Loading resources from init.sql
.timer on
COPY (FROM read_csv(getvariable('bucket') || '/large/orders.csv', strict_mode=false)) TO (getvariable('bucket') || '/large/orders.parquet') (FORMAT parquet);
Run Time (s): real 1.685 user 1.543060 sys 0.384162
SELECT count(*) FROM read_parquet(getvariable('bucket') || '/large/orders.parquet');
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│       252416 │
└──────────────┘
Run Time (s): real 0.051 user 0.021618 sys 0.003168
$ aws s3 --profile default ls s3://user-m-anane-ece/large/
2026-11-04 12:48:16   29574123 orders.csv
2026-11-04 12:48:18   11532863 orders.parquet
$ for query in "FROM read_csv(getvariable('bucket') || '/large/orders.csv', strict_mode=false) SELECT product, sum(quantity) GROUP BY product" "FROM read_parquet(getvariable('bucket') || '/large/orders.parquet') SELECT product, sum(quantity) GROUP BY product" "FROM read_parquet(getvariable('bucket') || '/large/orders.parquet') SELECT count(*) WHERE date >= '2100-01-01'"
$ echo 'FROM read_csv(getvariable('\''bucket'\'') || '\''/large/orders.csv'\'', strict_mode=false) SELECT product, sum(quantity) GROUP BY product'
FROM read_csv(getvariable('bucket') || '/large/orders.csv', strict_mode=false) SELECT product, sum(quantity) GROUP BY product
$ duckdb -init init.sql -c 'SET enable_external_file_cache = false; EXPLAIN ANALYZE FROM read_csv(getvariable('\''bucket'\'') || '\''/large/orders.csv'\'', strict_mode=false) SELECT product, sum(quantity) GROUP BY product'
$ grep -E 'in:|#GET|Total Time'
││            in: 28.2 MiB           ││
││              #GET: 1              ││
││               Total Time: 1.40s              ││
$ for query in "FROM read_csv(getvariable('bucket') || '/large/orders.csv', strict_mode=false) SELECT product, sum(quantity) GROUP BY product" "FROM read_parquet(getvariable('bucket') || '/large/orders.parquet') SELECT product, sum(quantity) GROUP BY product" "FROM read_parquet(getvariable('bucket') || '/large/orders.parquet') SELECT count(*) WHERE date >= '2100-01-01'"
$ echo 'FROM read_parquet(getvariable('\''bucket'\'') || '\''/large/orders.parquet'\'') SELECT product, sum(quantity) GROUP BY product'
FROM read_parquet(getvariable('bucket') || '/large/orders.parquet') SELECT product, sum(quantity) GROUP BY product
$ duckdb -init init.sql -c 'SET enable_external_file_cache = false; EXPLAIN ANALYZE FROM read_parquet(getvariable('\''bucket'\'') || '\''/large/orders.parquet'\'') SELECT product, sum(quantity) GROUP BY product'
$ grep -E 'in:|#GET|Total Time'
││           in: 203.5 KiB           ││
││              #GET: 4              ││
││              Total Time: 0.506s              ││
$ for query in "FROM read_csv(getvariable('bucket') || '/large/orders.csv', strict_mode=false) SELECT product, sum(quantity) GROUP BY product" "FROM read_parquet(getvariable('bucket') || '/large/orders.parquet') SELECT product, sum(quantity) GROUP BY product" "FROM read_parquet(getvariable('bucket') || '/large/orders.parquet') SELECT count(*) WHERE date >= '2100-01-01'"
$ echo 'FROM read_parquet(getvariable('\''bucket'\'') || '\''/large/orders.parquet'\'') SELECT count(*) WHERE date >= '\''2100-01-01'\'''
FROM read_parquet(getvariable('bucket') || '/large/orders.parquet') SELECT count(*) WHERE date >= '2100-01-01'
$ duckdb -init init.sql -c 'SET enable_external_file_cache = false; EXPLAIN ANALYZE FROM read_parquet(getvariable('\''bucket'\'') || '\''/large/orders.parquet'\'') SELECT count(*) WHERE date >= '\''2100-01-01'\'''
$ grep -E 'in:|#GET|Total Time'
││            in: 16.0 KiB           ││
││              #GET: 1              ││
││              Total Time: 0.400s              ││
$ duckdb -init init.sql -c 'SELECT row_group_id, row_group_num_rows, stats_min, stats_max FROM parquet_metadata(getvariable('\''bucket'\'') || '\''/large/orders.parquet'\'') WHERE path_in_schema = '\''date'\'''
-- Loading resources from init.sql
┌──────────────┬────────────────────┬───────────────────────────────┬───────────────────────────────┐
│ row_group_id │ row_group_num_rows │           stats_min           │           stats_max           │
│    int64     │       int64        │            varchar            │            varchar            │
├──────────────┼────────────────────┼───────────────────────────────┼───────────────────────────────┤
│            0 │             123573 │ 2020-01-01 00:44:02.671276+00 │ 2034-02-04 20:48:18.716516+00 │
│            1 │             124276 │ 2034-02-04 21:40:36.580081+00 │ 2048-04-10 00:20:51.481804+00 │
│            2 │               4567 │ 2048-04-10 01:37:01.674368+00 │ 2048-10-17 07:13:34.97841+00  │
└──────────────┴────────────────────┴───────────────────────────────┴───────────────────────────────┘
$ duckdb -init init.sql -c 'SELECT count(*) AS row_groups, count(*) FILTER (WHERE stats_max::TIMESTAMPTZ < TIMESTAMPTZ '\''2100-01-01'\'') AS skipped FROM parquet_metadata(getvariable('\''bucket'\'') || '\''/large/orders.parquet'\'') WHERE path_in_schema = '\''date'\'''
-- Loading resources from init.sql
┌────────────┬─────────┐
│ row_groups │ skipped │
│   int64    │  int64  │
├────────────┼─────────┤
│          3 │       3 │
└────────────┴─────────┘
$ uv add duckdb
Resolved 4 packages in 260ms
   Building ece-2026-bigdata @ file:///home/onyxia/work/ece-2026-bigdata
      Built ece-2026-bigdata @ file:///home/onyxia/work/ece-2026-bigdata
Downloading duckdb (23.2MiB)
 Downloaded duckdb
Prepared 2 packages in 268ms
Uninstalled 1 package in 0.51ms
Installed 2 packages in 10ms
 + duckdb==2.0.0
 ~ ece-2026-bigdata==0.1.0 (from file:///home/onyxia/work/ece-2026-bigdata)
$ cat
$ grep -q '^orders-report' pyproject.toml
$ sed -i '/^dataset-orders = /a orders-report = "ece_2026_bigdata.orders_report:main"' pyproject.toml
$ sed -n '/project.scripts/,/^$/p' pyproject.toml
[project.scripts]
dataset-users = "ece_2026_bigdata.dataset_users:main"
dataset-orders = "ece_2026_bigdata.dataset_orders:main"
orders-report = "ece_2026_bigdata.orders_report:main"

$ uv run orders-report
   Building ece-2026-bigdata @ file:///home/onyxia/work/ece-2026-bigdata
      Built ece-2026-bigdata @ file:///home/onyxia/work/ece-2026-bigdata
Uninstalled 1 package in 1ms
Installed 1 package in 10ms
2020-01	744	2297
2020-02	696	2154
2020-03	744	2192
2020-04	645	1972
$ uv run orders-report -p cookie
2020-01	128	376
2020-02	121	371
2020-03	122	340
2020-04	95	278
$ rm -f lab04_part1.sql lab04_part2.sql
$ git add .gitignore pyproject.toml uv.lock src
$ git commit -m 'feat: orders report with duckdb'
[main 4db43b1] feat: orders report with duckdb
 4 files changed, 56 insertions(+), 1 deletion(-)
 create mode 100644 src/ece_2026_bigdata/orders_report.py
$ gpush
$ git remote get-url origin
$ git push
fatal: The current branch main has no upstream branch.
To push the current branch and set the remote as upstream, use

    git push --set-upstream origin main

To have this happen automatically for branches without a tracking
upstream, see 'push.autoSetupRemote' in 'git help config'.

$ aws s3 --profile default rm s3://user-m-anane-ece/analytics/ --recursive
delete: s3://user-m-anane-ece/analytics/orders.parquet
delete: s3://user-m-anane-ece/analytics/orders_by_product/product=bread/data_0.parquet
delete: s3://user-m-anane-ece/analytics/orders_by_product/product=cookie/data_0.parquet
delete: s3://user-m-anane-ece/analytics/orders_by_product/product=brioche/data_0.parquet
delete: s3://user-m-anane-ece/analytics/orders_by_product/product=drink/data_0.parquet
delete: s3://user-m-anane-ece/analytics/orders_by_product/product=donut/data_0.parquet
delete: s3://user-m-anane-ece/analytics/orders_by_product/product=croissant/data_0.parquet
$ aws s3 --profile default rm s3://user-m-anane-ece/large/ --recursive
delete: s3://user-m-anane-ece/large/orders.parquet
delete: s3://user-m-anane-ece/large/orders.csv
$ rm -f orders_large.csv
$ aws s3 --profile default ls s3://user-m-anane-ece/ --recursive
2026-11-04 12:47:31     331568 bronze/orders.csv
2026-11-04 12:47:25       7351 bronze/users.csv
$ true
EXIT_CODE=0