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 to find the average tip amount and trip count for each taxi zone
 -- that has an average tip amount greater than $1.00 and more than 100 trips
 
 WITH good_tip_pickups AS (
  SELECT PULocationID, avg(tips) as avg_tip
  FROM trips
  GROUP BY PULocationID
  HAVING avg(tips) > 1.00
 ),
 high_frequency_pickups AS (
  SELECT PULocationID, count(1) as trip_count
  FROM trips
  GROUP BY PULocationID
  HAVING count(1) > 100
 )
 SELECT Zone, avg_tip, trip_count
 INTO table1
 FROM taxi_zone_lookup tzl
 JOIN good_tip_pickups gtp ON tzl.LocationID = gtp.PULocationID
 JOIN high_frequency_pickups hfp ON tzl.LocationID = hfp.PULocationID; 



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