Analyzing the Impact of Business Hour Mismatch on
Order Volume in the Food Delivery Industry: A Case
Study of UEats and Ghub
-- Create a temporary function to extract all the keys in a JSON object
CREATE TEMP FUNCTION jsonObjectKeys(input STRING)
RETURNS Array<String>
LANGUAGE js AS """
return Object.keys(JSON.parse(input));
""";
-- Step 1: Extract business hours from JSON for Uber Eats
WITH ubereats_hours AS (
SELECT
slug AS ue_slug,
b_name,
vb_name,
MAX(timestamp) AS latest_timestamp,
jsonObjectKeys((SELECT * FROM UNNEST(JSON_EXTRACT_SCALAR(response.data.menus,
'$.menuStructure')))) AS ue_hours
FROM `arboreal-vision-339901.take_home_v2.virtual_kitchen_ubereats_hours`
GROUP BY slug, b_name, vb_name
),
-- Step 2: Extract business hours from JSON for Grubhub
grubhub_hours AS (
SELECT
slug AS gh_slug,
b_name,
vb_name,
MAX(timestamp) AS latest_timestamp,
jsonObjectKeys((SELECT * FROM UNNEST(JSON_EXTRACT_SCALAR(response,
'$.availability_by_catalog.STANDARD_DELIVERY.schedule_rules')))) AS gh_hours
FROM `arboreal-vision-339901.take_home_v2.virtual_kitchen_grubhub_hours`
GROUP BY slug, b_name, vb_name
),
-- Step 3: Join the two tables on b_name and vb_name
joined_hours AS (
SELECT
gh.gh_slug,
gh.gh_hours AS virtual_restaurant_business_hours,
ue.ue_slug,
ue.ue_hours AS ubereats_business_hours
FROM grubhub_hours gh
JOIN ubereats_hours ue
ON gh.b_name = ue.b_name AND gh.vb_name = ue.vb_name
)
-- Step 4: Compute the business hour mismatch and output the final table
SELECT
gh_slug,
virtual_restaurant_business_hours,
ue_slug,
ubereats_business_hours,
CASE
WHEN virtual_restaurant_business_hours = ubereats_business_hours THEN 'In Range'
WHEN ABS(TIMESTAMP_DIFF(virtual_restaurant_business_hours,
ubereats_business_hours, MINUTE)) <= 5 THEN 'Out of Range with 5 mins difference'
ELSE 'Out of Range'
END AS is_out_of_range
FROM joined_hours
