version: '3'
  services:
    materialize:
      image: materialize/materialized:v0.5.3
      container_name: "materialize-sandbox-docker-compose"
      volumes:
        - ./data:/data
      ports:
        - "6875:6875"

docker-compose up

psql -h localhost -p 6875 materialize

psql (12.5 (Ubuntu 12.5-0ubuntu0.20.04.1), server 9.5.0)
Type "help" for help.

materialize=>

CREATE SOURCE activities_detailed_source
FROM FILE '/data/activities-detailed-all.json'
WITH(tail=true)
FORMAT TEXT;

CREATE VIEW activities_detailed AS
SELECT  (val->>'id')::float::bigint AS id,
        (val->>'start_date')::timestamp AS start_date,
        (val->'best_efforts') AS best_efforts,
        (val->'splits_metric') AS splits_metric,
        (val->'splits_standard') AS splits_standard,
        (val->'segment_efforts') AS segment_efforts
FROM (SELECT text::json AS val FROM activities_detailed_source);

SELECT *
FROM activities_detailed
LIMIT 1
AS OF 1;

CREATE MATERIALIZED VIEW best_efforts AS
SELECT id, start_date,
       (effort->>'effort_name') AS effort_name,
       (effort->>'effort_distance')::float AS distance,
       (effort->>'elapsed_time')::float AS elapsed_time,
       (effort->>'moving_time')::float AS moving_time,
       (effort->>'average_heartrate')::float AS average_heartrate,
       (effort->>'max_heartrate')::float AS max_heartrate
FROM  (
  SELECT id, start_date, best_efforts
  FROM activities_detailed
), jsonb_array_elements(best_efforts) AS effort;

CREATE MATERIALIZED VIEW segment_efforts AS
SELECT id, start_date,
       (effort->>'segment_id') AS segment_id,
       (effort->>'segment_name') AS segment_name,
       (effort->>'distance')::float AS distance,
       (effort->>'elapsed_time')::float AS elapsed_time,
       (effort->>'moving_time')::float AS moving_time
FROM  (
  SELECT id, start_date, segment_efforts
  FROM activities_detailed
), jsonb_array_elements(segment_efforts) AS effort;

select start_date, to_char(to_timestamp(moving_time), 'HH24:MI:SS') AS time
from best_efforts
WHERE effort_name = '10k'
ORDER BY moving_time
LIMIT 10;

select start_date,
       effort_name,
       to_char(to_timestamp(moving_time), 'MI:SS') AS time
from best_efforts
WHERE id=2448908609
ORDER BY moving_time
LIMIT 10;

select segment_id, segment_name, count(*) AS count
from segment_efforts
GROUP BY segment_id, segment_name
ORDER BY count DESC
LIMIT 5;

SELECT start_date,
       to_char(to_timestamp(min(moving_time)), 'MI:SS') AS fastestTime,
       to_char(to_timestamp(max(moving_time)), 'MI:SS') AS slowestTime,
       to_char(to_timestamp(avg(moving_time)), 'MI:SS') AS averageTime,
       count(*) AS lapsDone
FROM segment_efforts
WHERE segment_id = '17236468'
GROUP BY start_date
ORDER BY min(moving_time)
LIMIT 5;