Snowflake Reader Account Example Queries

To get the latest odometer readings for a vehicle on a specific day, find the TID in the TID Information table, above. You can then write a select query that may resemble the following query:

SELECT VEHICLE_KEY, DIAGS['0'], TIME FROM "VZC"."PUBLIC"."POINT_WITH_VEHICLE" 
WHERE VEHICLE_KEY = vehicle_key_1 AND TIME > '2020-01-01' AND TIME < '2020-01-02' AND DIAGS['0'] IS NOT NULL 
ORDER BY TIME LIMIT 1;

Alternatively, if you do not know the key but would like to filter out the vehicle by name or registration number, you can join to the vehicle view:

SELECT V.VEHICLE_KEY, V.VEHICLE_NAME, PWE.DIAGS['0'], PWE.TIME FROM "VZC"."PUBLIC"."POINT_WITH_VEHICLE" PWE
JOIN "VZC"."PUBLIC"."VEHICLE" V ON PWE.VEHICLE_KEY = V.VEHICLE_KEY
WHERE V.VEHICLE_NAME = 'vehicle_name_1' AND PWE.TIME > '2020-01-01' AND PWE.TIME < '2020-01-02' AND PWE.DIAGS['0'] IS NOT NULL
ORDER BY PWE.TIME LIMIT 1;

You can query KPIs for a particular week for a specific driver. The following code block shows an example query:

SELECT D.DRIVER_KEY, D.DRIVER_NAME, D.HIERARCHY, DD.DATE, DD.DISTANCE, DD.ENGINE_TIME, DD.FUEL_USED FROM "VZC"."PUBLIC"."DRIVER_DAY" DD
JOIN "VZC"."PUBLIC"."DRIVER" D ON DD.DRIVER_KEY = D.DRIVER_KEY
WHERE DD.DATE > '2020-01-01' AND DD.DATE < '2020-01-07' AND D.DRIVER_KEY = driver_key_1 [AND D.DRIVER_NAME = 'driver_name_1']
ORDER BY DD.DATE;

For a hierarchy you can add aggregation to KPIs:

SELECT D.HIERARCHY, DD.DATE, SUM(DD.DISTANCE), SUM(DD.ENGINE_TIME), SUM(DD.FUEL_USED) FROM "VZC"."PUBLIC"."DRIVER_DAY" DD
JOIN "VZC"."PUBLIC"."DRIVER" D ON DD.DRIVER_KEY = D.DRIVER_KEY
WHERE DD.DATE > '2020-01-01' AND DD.DATE < '2020-01-07' AND ARRAY_CONTAINS('hierarchy_name_1'::VARIANT, D.HIERARCHY)
GROUP BY D.HIERARCHY, DD.DATE
ORDER BY DD.DATE;

SELECT A.STATE, T.VEHICLE_KEY, SUM(T.DISTANCE), SUM(T.ENGINE_TIME), SUM(T.FUEL_USED) FROM "VZC"."PUBLIC"."TRIP_BY_STATE" T
JOIN "VZC"."PUBLIC"."ADDRESS" A ON A.ADDRESS_ID = T.FIRST_ADDRESS_ID
JOIN "VZC"."PUBLIC"."VEHICLE" V ON V.VEHICLE_KEY = T.VEHICLE_KEY
WHERE T.START_TIME_UTC > '2020-01-01' AND T.START_TIME_UTC < '2020-01-10'
GROUP BY A.STATE, T.VEHICLE_KEY;

SELECT M.MARKER_NAME, V.VEHICLE_NAME, T.TIME_ON_SITE FROM "VZC"."PUBLIC"."TIME_ON_SITE" T
JOIN "VZC"."PUBLIC"."MARKER" M ON M.MARKER_KEY = T.MARKER_KEY
JOIN "VZC"."PUBLIC"."VEHICLE" V ON V.VEHICLE_KEY = T.VEHICLE_KEY
WHERE T.ENTRY_TIMESTAMP_UTC > '2020-01-01' AND T.ENTRY_TIMESTAMP_UTC < '2020-01-10' AND VEHICLE_NAME = 'VEHICLE_NAME_1' AND MARKER_NAME = 'MARKER_NAME_1';

CREATE DATABASE TEST_DB;
CREATE VIEW TEST_DB.PUBLIC.EAST_VEHICLES AS
SELECT
    vehicle_id,
    vehicle_name,
    hierarchy
FROM VZC.PUBLIC.VEHICLE
WHERE hierarchy[1] = 'East Division';

SELECT * FROM TEST_DB.PUBLIC.EAST_VEHICLES;

CREATE ROLE EAST_DIVISION;
GRANT USAGE ON DATABASE TEST_DB TO ROLE EAST_DIVISION;
GRANT USAGE ON SCHEMA TEST_DB.PUBLIC TO ROLE EAST_DIVISION;
GRANT USAGE ON WAREHOUSE VZC_WH TO ROLE EAST_DIVISION;
GRANT SELECT ON TEST_DB.PUBLIC.EAST_VEHICLES TO ROLE EAST_DIVISION;

GRANT IMPORTED PRIVILEGES ON DATABASE VZC TO ROLE NEW_ROLE;

CREATE USER TEST_USER PASSWORD='***' DEFAULT_WAREHOUSE = BI_PROD_WH DEFAULT_NAMESPACE = VZC.PUBLIC DEFAULT_ROLE = EAST_DIVISION;
GRANT ROLE EAST_DIVISION TO USER TEST_USER;