dozer-config.yaml

app_name: sql-join-sample
version: 1

connections:
  - config : !LocalStorage
      details:
        path: ../data
      tables:
        - !Table
          name: taxi_zone_lookup
          config: !CSV
            path: zones
            extension: .csv
        - !Table
          name: trips
          config: !Parquet
            path: trips
            extension: .parquet
    name: ny_taxi

sources: 
  - name: taxi_zone_lookup
    table_name: taxi_zone_lookup
    connection: ny_taxi

  - name: trips
    table_name: trips
    connection: ny_taxi

sql: |

  -- Query that calculates the average tips, along with the totaltrips made, for each zone
  -- Also filters out zones with average tips less than $1.50  
  
  SELECT zp.Zone, ROUND(AVG(t.tips),2) as avg_tips, COUNT(1) as trip_count
  INTO table1
  FROM trips t
  JOIN taxi_zone_lookup zp ON t.PULocationID = zp.LocationID
  GROUP BY zp.Zone
  HAVING ROUND(AVG(t.tips),2) > 1.50;

endpoints:
  - name: intrazone-tips
    path: /intrazone-tips
    table_name: table1