version: '2.1'

services:

  graphql-engine:

    image: hasura/graphql-engine:v1.0.0-alpha26

    ports:

    - "8080:8080"

    command: >

      /bin/sh -c "

      graphql-engine --database-url postgres://postgres@master/postgres serve --enable-console;

      "

  master:

    container_name: "${COMPOSE_PROJECT_NAME:-citus}_master"

    image: 'citusdata/citus:7.5.1'

    ports: ["${MASTER_EXTERNAL_PORT:-5432}:5432"]

    labels: ['com.citusdata.role=Master']

  worker:

    image: 'citusdata/citus:7.5.1'

    labels: ['com.citusdata.role=Worker']

    depends_on: { manager: { condition: service_healthy } }

  manager:

    container_name: "${COMPOSE_PROJECT_NAME:-citus}_manager"

    image: 'citusdata/membership-manager:0.2.0'

    volumes: ['/var/run/docker.sock:/var/run/docker.sock']

    depends_on: { master: { condition: service_healthy } }

curl https://examples.citusdata.com/tutorial/companies.csv > companies.csv

curl https://examples.citusdata.com/tutorial/campaigns.csv > campaigns.csv

curl https://examples.citusdata.com/tutorial/ads.csv > ads.csv

CREATE TABLE companies (

    id bigint NOT NULL,

    name text NOT NULL,

    image_url text,

    created_at timestamp without time zone NOT NULL,

    updated_at timestamp without time zone NOT NULL

);

CREATE TABLE campaigns (

    id bigint NOT NULL,

    company_id bigint NOT NULL,

    name text NOT NULL,

    cost_model text NOT NULL,

    state text NOT NULL,

    monthly_budget bigint,

    blacklisted_site_urls text[],

    created_at timestamp without time zone NOT NULL,

    updated_at timestamp without time zone NOT NULL

);

CREATE TABLE ads (

    id bigint NOT NULL,

    company_id bigint NOT NULL,

    campaign_id bigint NOT NULL,

    name text NOT NULL,

    image_url text,

    target_url text,

    impressions_count bigint DEFAULT 0,

    clicks_count bigint DEFAULT 0,

    created_at timestamp without time zone NOT NULL,

    updated_at timestamp without time zone NOT NULL

);

ALTER TABLE companies ADD PRIMARY KEY (id);

ALTER TABLE campaigns ADD PRIMARY KEY (id, company_id);

ALTER TABLE ads ADD PRIMARY KEY (id, company_id);

SELECT create_distributed_table('companies', 'id');

SELECT create_distributed_table('campaigns', 'company_id');

SELECT create_distributed_table('ads', 'company_id');

SELECT campaigns.id, campaigns.name, campaigns.monthly_budget,

       sum(impressions_count) as total_impressions, sum(clicks_count) as total_clicks

FROM ads, campaigns

WHERE ads.company_id = campaigns.company_id

AND campaigns.company_id = 5

AND campaigns.state = 'running'

GROUP BY campaigns.id, campaigns.name, campaigns.monthly_budget

ORDER BY total_impressions, total_clicks;