Netflix Business Model, Revenue & Cost Structure Questions

-- 1) Recent shared IPs: count distinct shared IPs in last 90 days
WITH recent_ips AS (
  SELECT d.user_id, di.ip
  FROM devices d JOIN device_ips di USING(device_id)
  WHERE di.seen_at >= now() - interval '90 days'
  GROUP BY d.user_id, di.ip
),
shared_ip_pairs AS (
  SELECT r1.user_id AS u1, r2.user_id AS u2, COUNT(*) AS shared_ip_cnt
  FROM recent_ips r1 JOIN recent_ips r2 USING(ip)
  WHERE r1.user_id < r2.user_id
  GROUP BY u1,u2
),
-- 2) Shared devices (same device_id used by multiple users)
device_pairs AS (
  SELECT d1.user_id AS u1, d2.user_id AS u2, COUNT(DISTINCT d1.device_id) AS shared_device_cnt
  FROM devices d1 JOIN devices d2 USING(device_id)
  WHERE d1.user_id < d2.user_id
  GROUP BY u1,u2
),
-- 3) Email domain match
email_domain AS (
  SELECT user_id, split_part(email,'@',2) AS domain FROM accounts
),
domain_pairs AS (
  SELECT e1.user_id AS u1, e2.user_id AS u2, (e1.domain = e2.domain)::int AS same_domain
  FROM email_domain e1 JOIN email_domain e2 ON e1.user_id < e2.user_id
),
-- 4) Billing address match (if available)
billing_pairs AS (
  SELECT b1.user_id AS u1, b2.user_id AS u2, (b1.billing_address = b2.billing_address)::int AS same_billing
  FROM billing b1 JOIN billing b2 ON b1.user_id < b2.user_id
)
-- Combine signals into score
SELECT
  COALESCE(sip.u1, dp.u1, dp2.u1, bp.u1) AS u1,
  COALESCE(sip.u2, dp.u2, dp2.u2, bp.u2) AS u2,
  COALESCE(sip.shared_ip_cnt,0) * 2
  + COALESCE(dp.shared_device_cnt,0) * 5
  + COALESCE(dp2.same_domain,0) * 1
  + COALESCE(bp.same_billing,0) * 10 AS score
FROM shared_ip_pairs sip
FULL JOIN device_pairs dp USING(u1,u2)
FULL JOIN domain_pairs dp2 USING(u1,u2)
FULL JOIN billing_pairs bp USING(u1,u2)
WHERE COALESCE(sip.shared_ip_cnt,0)>0 OR COALESCE(dp.shared_device_cnt,0)>0 OR COALESCE(dp2.same_domain,0)>0 OR COALESCE(bp.same_billing,0)>0;