Skip to main content
These queries assume you named your database VETCOVE_DATA. Adjust the database name if you chose a different name during setup.

​
PHD Clinic Overview

Get a summary of all clinics using PIMS and/or Home Delivery in your organization: 
SELECT
    clinic_id,
    name as clinic_name,
    client_display_name,
    primary_address_city,
    primary_address_state,
    clinic_timezone,
    hd_launched_at,
    last_updated,
    vetcove_corporate_id
FROM vetcove_corporate_data_share_beta.phd_enriched.clinic_enriched
WHERE is_deleted = FALSE
ORDER BY clinic_name;

​
Home Delivery Order Volume by Clinic

See monthly order counts and totals per clinic: 
SELECT
    c.name as clinic_name,
    DATE_TRUNC('month', o.order_placed_datetime) AS order_month,
    COUNT(*) AS order_count,
    SUM(o.total) AS total_pet_parent_revenue
FROM vetcove_corporate_data_share_beta.phd_enriched.orders_enriched o
JOIN vetcove_corporate_data_share_beta.phd_enriched.clinic_enriched c ON o.clinic_id = c.clinic_id
WHERE o.is_deleted = FALSE
    AND o.order_placed_datetime >= DATEADD('month', -6, CURRENT_DATE())
GROUP BY clinic_name, order_month
ORDER BY clinic_name, order_month;

​
Ratio of HD sales to B2B by Clinic

See how much pet parent purchases on Home Delivery clinics stack up against total B2B purchases by clinic in a given month. 
WITH hd_totals AS (
    SELECT
        c.vetcove_buyer_id AS buyer_id,
        SUM(o.total) AS hd_total
    FROM vetcove_corporate_data_share_beta.phd_enriched.orders_enriched o
    LEFT JOIN vetcove_corporate_data_share_beta.phd_enriched.clinic_enriched c
        ON o.clinic_id = c.clinic_id
    WHERE o.is_deleted = FALSE
        AND DATE_TRUNC('month', o.order_placed_datetime::DATE)
            = DATE_TRUNC('month', DATEADD('month', -1, CURRENT_DATE()))
    GROUP BY c.vetcove_buyer_id
),

b2b_totals AS (
    SELECT
        so.buyer_id,
        SUM(so.total) AS b2b_total
    FROM vetcove_corporate_data_share_beta.b2b_enriched.supplier_orders_enriched so
    WHERE so.is_deleted = FALSE
        AND DATE_TRUNC('month', so.date)
            = DATE_TRUNC('month', DATEADD('month', -1, CURRENT_DATE()))
    GROUP BY so.buyer_id
)

SELECT
    b.name AS buyer_name,
    COALESCE(hd.hd_total, 0) AS hd_sales,
    COALESCE(b2b.b2b_total, 0) AS b2b_procurement,
    CASE
        WHEN COALESCE(b2b.b2b_total, 0) = 0 THEN NULL
        ELSE ROUND(hd.hd_total / b2b.b2b_total, 4)
    END AS hd_to_b2b_ratio
FROM vetcove_corporate_data_share_beta.b2b_enriched.buyer_enriched b
LEFT JOIN hd_totals hd ON b.buyer_id = hd.buyer_id
LEFT JOIN b2b_totals b2b ON b.buyer_id = b2b.buyer_id
WHERE b.is_deleted = FALSE
    AND (hd.hd_total > 0 AND b2b.b2b_total > 0)
ORDER BY b2b_procurement DESC;