Cross-Product Queries

-- Jarvis: "Show me my top selling products"
SELECT name, total_sales, inventory_count
FROM shopify_products
ORDER BY total_sales DESC
LIMIT 5;

-- Publisher: Travel blog sidebar widget
SELECT name, image_url, price, affiliate_url
FROM shopify_products
WHERE vertical = 'travel' AND in_stock = true;

-- Agency: Client performance dashboard
SELECT 
  ac.company_name,
  COUNT(so.id) as orders,
  SUM(so.total) as revenue
FROM agency_clients ac
JOIN shopify_orders so ON so.client_id = ac.id
GROUP BY ac.id;

-- Which blog posts drive Viator bookings?
SELECT 
  pp.title as article,
  pv.name as vertical,
  COUNT(vc.id) as clicks,
  SUM(vc.commission_earned) as revenue
FROM publisher_posts pp
JOIN publisher_verticals pv ON pp.vertical_id = pv.id
JOIN publisher_post_viator_products ppvp ON ppvp.post_id = pp.id
JOIN viator_click_tracking vc ON vc.product_id = ppvp.viator_product_id
WHERE vc.converted = true
GROUP BY pp.id, pv.id
ORDER BY revenue DESC;

-- Match creators to products by vertical alignment
SELECT 
  cp.display_name as creator,
  cp.audience_size,
  sp.name as product,
  sp.commission_rate
FROM creator_profiles cp
JOIN content_analysis ca ON ca.creator_id = cp.id
JOIN shopify_products sp ON sp.vertical = ca.primary_vertical
WHERE cp.verified = true
  AND sp.in_stock = true
  AND ca.engagement_rate > 0.03;

-- Full user journey: visitor → reader → customer → creator
SELECT 
  u.email,
  COUNT(DISTINCT ae.id) as page_views,
  COUNT(DISTINCT so.id) as purchases,
  SUM(so.total) as lifetime_value,
  cp.creator_tier,
  SUM(clp.amount) as licensing_revenue
FROM users u
LEFT JOIN audience_events ae ON ae.user_id = u.id
LEFT JOIN shopify_orders so ON so.customer_email = u.email
LEFT JOIN creator_profiles cp ON cp.user_id = u.id
LEFT JOIN creator_license_purchases clp ON clp.creator_id = cp.id
GROUP BY u.id, cp.creator_tier;

-- How do agency clients compare to our own verticals?
WITH our_performance AS (
  SELECT 
    'Internal' as source,
    pv.name as vertical,
    AVG(ppa.page_views) as avg_views,
    AVG(ppa.conversion_rate) as avg_conversion
  FROM publisher_verticals pv
  JOIN publisher_posts pp ON pp.vertical_id = pv.id
  JOIN publisher_post_analytics ppa ON ppa.post_id = pp.id
  GROUP BY pv.id
),
client_performance AS (
  SELECT 
    'Client: ' || ac.company_name as source,
    ad.vertical,
    AVG(ad.page_views) as avg_views,
    AVG(ad.conversion_rate) as avg_conversion
  FROM agency_clients ac
  JOIN agency_deliverables ad ON ad.client_id = ac.id
  GROUP BY ac.id, ad.vertical
)
SELECT * FROM our_performance
UNION ALL
SELECT * FROM client_performance
ORDER BY vertical, source;

-- Jarvis query: "How's the business doing this week?"
SELECT 
  'Publisher' as unit,
  COUNT(*) as activity,
  'posts published' as metric
FROM publisher_posts 
WHERE created_at > NOW() - INTERVAL '7 days'

UNION ALL

SELECT 
  'Store',
  COUNT(*),
  'orders'
FROM shopify_orders 
WHERE created_at > NOW() - INTERVAL '7 days'

UNION ALL

SELECT 
  'Platform',
  COUNT(*),
  'licenses sold'
FROM creator_license_purchases 
WHERE created_at > NOW() - INTERVAL '7 days'

UNION ALL

SELECT 
  'Agency',
  COUNT(*),
  'deliverables completed'
FROM agency_deliverables 
WHERE completed_at > NOW() - INTERVAL '7 days';