Influxdb based sensor with derivative just won't work / how to make query?

Configuration
1

So I’ve got some data I’m pulling from my router that’s logged to Influxdb…

According to Influx 1.x documentation you should just be able to add the query to the where or the measurement fields, but my sensor just returns a 0 value…

An example of a query directly in the InfluxDB Shell:

SELECT derivative(first(bytes_recv), 1s) FROM "net" WHERE "interface" = 'pppoe0' AND time > now() - 1h GROUP BY time(1s)

Returns for me how many bytes p/sec are passing through the interface…

name: net
time                derivative
----                ----------
1618056720000000000 1073399.6
1618056730000000000 1814794.6
1618056740000000000 1132648
etc ...

Now trying to adapt this to a InfluxDB query has me totally stumped…
Here’s what I’m trying currently, but it’s just returning a 0 value still:

- platform: influxdb
  host: localhost
  username: pfsense
  password: xxx
  database: pfsense
  queries:

    - name: pfSense_wan_throughput_in
      measurement: '(SELECT derivative(first(bytes_recv), 1s) FROM "net" WHERE "interface" = ''pppoe0'' AND time > now() - 1h GROUP BY time(1s))'
      where: '"time"  > now() - 1h'
      field: "derivative"
      value_template: "{{ (value | int / 131072) | round(2) }}"
      unit_of_measurement: MB/s
      group_function: last

My understanding is that the above YAML should produce a query that looks something like…

select derivative from (SELECT derivative(first(bytes_recv), 1s) FROM "net" WHERE "interface" = 'pppoe0' AND time > now() - 1h GROUP BY time(1s)) where "time" > now() - 1h limit 1

Which returns the expected results …

name: net
time                derivative
----                ----------
1618058740000000000 657227

Yet my HQ sensor is still just reporting a sweet “0”…

Anyone seen this before / have any suggestions?

Thanks,