Graph Analytics

-- Single shortest path
MATCH path = SHORTEST (a:Person {name: 'Alice'})-[:KNOWS*]->(b:Person {name: 'Bob'})
RETURN path, length(path) AS hops;

-- All shortest paths (when multiple exist)
MATCH paths = ALL SHORTEST (a:City {name: 'San Francisco'})
  -[:FLIGHT*]->
  (b:City {name: 'New York'})
RETURN paths,
  [r IN relationships(paths) | r.airline] AS airlines,
  null /* reduce() is advertised by SHOW FUNCTIONS but has no evaluator dispatch; collect() the values and fold client-side (for path weights use algo.dijkstra.stream, which yields cost) */ AS total_cost;

-- Top 3 shortest paths with total distance
MATCH paths = K SHORTEST 3
  (start:Location {id: 'warehouse_1'})
  -[:ROUTE*]->
  (end:Location {id: 'customer_42'})
RETURN paths,
  null /* reduce() is advertised by SHOW FUNCTIONS but has no evaluator dispatch; collect() the values and fold client-side (for path weights use algo.dijkstra.stream, which yields cost) */ AS total_distance
ORDER BY total_distance;

-- Find paths through active accounts only
MATCH path = (sender:Account {id: $from})
  -[:TRANSFER*2..5 (r, n | n.status = 'active' AND r.amount > 1000)]->
  (receiver:Account {id: $to})
WHERE all(r IN relationships(path) WHERE r.timestamp >= DATE '2024-01-01')
RETURN path;

-- Find most connected users
MATCH (u:User)
OPTIONAL MATCH (u)-[r:FOLLOWS]-()
RETURN u.id, u.name, count(r) AS connections
ORDER BY connections DESC
LIMIT 20;

-- In-degree vs out-degree
MATCH (u:User)
OPTIONAL MATCH (u)<-[:FOLLOWS]-(followers)
OPTIONAL MATCH (u)-[:FOLLOWS]->(following)
RETURN
  u.name,
  count(DISTINCT followers) AS follower_count,
  count(DISTINCT following) AS following_count,
  count(DISTINCT followers) * 1.0 / nullif(count(DISTINCT following), 0) AS follower_ratio
ORDER BY follower_count DESC;

-- Identify key intermediaries in network
CALL algo.betweenness.stream()
YIELD nodeId, betweenness
MATCH (p:Person) WHERE id(p) = nodeId
RETURN p.name, betweenness
ORDER BY betweenness DESC
LIMIT 10;

-- Calculate PageRank scores
CALL algo.pagerank.stream()
YIELD nodeId, score
MATCH (page:WebPage) WHERE id(page) = nodeId
RETURN page.url, score
ORDER BY score DESC
LIMIT 50;

-- Social influence ranking
CALL algo.pagerank.stream()
YIELD nodeId, score
MATCH (u:User) WHERE id(u) = nodeId AND score > 0.01
RETURN u.name, score, u.followers_count
ORDER BY score DESC;

-- Detect communities in social network
CALL algo.labelPropagation.stream()
YIELD nodeId, label
MATCH (u:User) WHERE id(u) = nodeId
RETURN label, collect(u.name) AS members, count(u) AS size
ORDER BY size DESC;

-- Identify isolated clusters
CALL algo.unionFind.stream()
YIELD nodeId, setId
MATCH (n:Node) WHERE id(n) = nodeId
WITH setId, count(n) AS size, collect(n.id) AS nodes
WHERE size > 1
RETURN setId, size, nodes
ORDER BY size DESC;

-- High-quality community detection
CALL algo.louvain.stream()
YIELD nodeId, community
MATCH (u:User) WHERE id(u) = nodeId
RETURN
  community,
  collect(u.name) AS members,
  count(u) AS size
ORDER BY size DESC;

-- Find triangles in social network
MATCH (a:Person)-[:KNOWS]->(b:Person)-[:KNOWS]->(c:Person)-[:KNOWS]->(a)
WHERE a.id < b.id AND b.id < c.id  -- Avoid duplicates
RETURN count(*) AS triangle_count;

-- Triangles involving specific user
MATCH (a:Person {name: 'Alice'})-[:KNOWS]->(b)-[:KNOWS]->(c)-[:KNOWS]->(a)
RETURN DISTINCT b.name, c.name;

-- Detect circular dependencies in task graph
MATCH path = (t:Task)-[:DEPENDS_ON*2..10]->(t)
RETURN path, length(path) AS cycle_length
ORDER BY cycle_length;

-- Find "star" pattern: one person followed by many
MATCH (influencer:Person)<-[:FOLLOWS]-(followers:Person)
WITH influencer, collect(followers) AS follower_list
WHERE size(follower_list) > 100
RETURN influencer.name, size(follower_list) AS follower_count
ORDER BY follower_count DESC;

-- Find "bridge" pattern: users connecting different communities
MATCH (a:Person)-[:KNOWS]->(bridge:Person)-[:KNOWS]->(b:Person)
WHERE a.community_id <> b.community_id
  AND a.community_id <> bridge.community_id
  AND b.community_id <> bridge.community_id
RETURN bridge.name, count(DISTINCT a.community_id) AS communities_bridged
ORDER BY communities_bridged DESC;

-- Sales analytics
MATCH (p:Product)<-[:PURCHASED]-(u:User)
RETURN
  p.category,
  count(u) AS total_purchases,
  count(DISTINCT u) AS unique_customers,
  avg(p.price) AS avg_price,
  sum(p.price) AS total_revenue
GROUP BY p.category
ORDER BY total_revenue DESC;

-- Price distribution analysis
MATCH (p:Product)
WHERE p.category = 'Electronics'
RETURN
  p.subcategory,
  count(*) AS product_count,
  avg(p.price) AS mean_price,
  collect(p.price) /* stddev/variance parse but are never computed; reduce client-side */ AS price_stddev,
  collect(p.price) /* percentile_cont/_disc parse but are never computed; sort client-side */ AS q1_price,
  collect(p.price) /* percentile_cont/_disc parse but are never computed; sort client-side */ AS median_price,
  collect(p.price) /* percentile_cont/_disc parse but are never computed; sort client-side */ AS q3_price,
  collect(p.price) /* percentile_cont/_disc parse but are never computed; sort client-side */ AS p95_price,
  min(p.price) AS min_price,
  max(p.price) AS max_price
GROUP BY p.subcategory;

-- Rank products within categories
MATCH (p:Product)
RETURN
  p.category,
  p.name,
  p.sales,
  null /* Geode has no window functions and no OVER clause */ AS rank_in_category,
  p.sales - avg(p.sales) AS deviation_from_avg
ORDER BY p.category, rank_in_category;

-- Sales trends over time
MATCH (s:Sale)
WHERE s.timestamp >= DATE '2024-01-01'
RETURN
  $bucket_start /* no date_trunc(): bucket at write time or compare an explicit range */ AS month,
  count(*) AS sale_count,
  sum(s.amount) AS revenue,
  avg(s.amount) AS avg_order_value
GROUP BY month
ORDER BY month;

-- User activity patterns by day of week
MATCH (u:User)-[:LOGGED_IN]->(session:Session)
RETURN
  extract(dow from session.timestamp) AS day_of_week,
  session.timestamp.hour AS hour,
  count(*) AS session_count
GROUP BY day_of_week, hour
ORDER BY day_of_week, hour;

-- Identify sudden spikes in activity
WITH
  DATE '2024-01-01' AS start_date,
  DATE '2024-12-31' AS end_date
MATCH (e:Event)
WHERE e.timestamp >= start_date AND e.timestamp <= end_date
WITH
  $bucket_start /* no date_trunc(): bucket at write time or compare an explicit range */ AS day,
  count(*) AS daily_count
WITH
  day,
  daily_count,
  avg(daily_count) AS moving_avg
WHERE daily_count > moving_avg * 2  -- Spike: 2x the moving average
RETURN day, daily_count, moving_avg
ORDER BY day;

-- Recent activity dashboard (last 15 minutes)
MATCH (u:User)-[:ACTION]->(a:Action)
WHERE a.timestamp >= CURRENT_TIMESTAMP - DURATION 'PT15M'
RETURN
  a.type,
  count(*) AS action_count,
  count(DISTINCT u) AS unique_users,
  max(a.timestamp) AS last_occurrence
GROUP BY a.type
ORDER BY action_count DESC;

-- Active sessions right now
MATCH (u:User)-[:HAS_SESSION]->(s:Session)
WHERE s.started <= CURRENT_TIMESTAMP
  AND (s.ended IS NULL OR s.ended >= CURRENT_TIMESTAMP)
RETURN count(DISTINCT u) AS active_users, count(s) AS active_sessions;

-- RFM analysis (Recency, Frequency, Monetary)
MATCH (c:Customer)-[:MADE]->(o:Order)
WITH c,
  max(o.date) AS last_order_date,
  count(o) AS order_count,
  sum(o.total) AS total_spent
RETURN
  c.id,
  c.name,
  CURRENT_DATE - last_order_date AS days_since_last_order,
  order_count,
  total_spent,
  CASE
    WHEN days_since_last_order <= 30 AND total_spent > 1000 THEN 'VIP'
    WHEN days_since_last_order <= 90 AND order_count > 5 THEN 'Loyal'
    WHEN days_since_last_order > 180 THEN 'At Risk'
    ELSE 'Regular'
  END AS segment
ORDER BY total_spent DESC;

-- Calculate CLV projection
MATCH (c:Customer)-[:MADE]->(o:Order)
WITH c,
  count(o) AS order_count,
  sum(o.total) AS total_spent,
  avg(o.total) AS avg_order_value,
  min(o.date) AS first_order,
  max(o.date) AS last_order
WITH c, order_count, total_spent, avg_order_value,
  (last_order - first_order) AS customer_age_days,
  (order_count * 1.0 / nullif((last_order - first_order), 0)) AS orders_per_day
RETURN
  c.id,
  total_spent AS historical_value,
  avg_order_value * orders_per_day * 365 AS projected_annual_value,
  total_spent + (avg_order_value * orders_per_day * 365 * 3) AS projected_3yr_ltv
ORDER BY projected_3yr_ltv DESC
LIMIT 100;

-- Find similar users based on purchase behavior
MATCH (u1:User {id: $user_id})-[:PURCHASED]->(p:Product)<-[:PURCHASED]-(u2:User)
WHERE u1 <> u2
WITH u2, count(DISTINCT p) AS common_products
ORDER BY common_products DESC
LIMIT 10
MATCH (u2)-[:PURCHASED]->(recommended:Product)
WHERE NOT EXISTS {
  MATCH (u1)-[:PURCHASED]->(recommended)
}
RETURN
  recommended.name,
  count(u2) AS recommended_by,
  avg(recommended.rating) AS avg_rating
GROUP BY recommended.name
ORDER BY recommended_by DESC, avg_rating DESC
LIMIT 20;

-- Recommend similar products
MATCH (p:Product {id: $product_id})
MATCH (similar:Product)
WHERE similar <> p
  AND similar.category = p.category
  AND abs(similar.price - p.price) < 50
OPTIONAL MATCH (similar)<-[:PURCHASED]-(u:User)-[:PURCHASED]->(p)
RETURN
  similar.name,
  similar.price,
  count(u) AS also_bought_count,
  similar.rating
ORDER BY also_bought_count DESC, similar.rating DESC
LIMIT 10;

-- Detect suspicious transaction patterns
MATCH (a1:Account)-[t1:TRANSFER]->(a2:Account)-[t2:TRANSFER]->(a3:Account)
WHERE t1.timestamp >= CURRENT_TIMESTAMP - DURATION 'PT1H'
  AND t2.timestamp > t1.timestamp
  AND t2.timestamp <= t1.timestamp + DURATION 'PT5M'
  AND t1.amount > 10000
  AND t2.amount >= t1.amount * 0.9
RETURN
  a1.id AS source,
  a2.id AS intermediary,
  a3.id AS destination,
  t1.amount AS initial_amount,
  t2.amount AS transfer_amount,
  t2.timestamp - t1.timestamp AS time_diff
ORDER BY initial_amount DESC;

-- Analyze supply chain dependencies
MATCH path = (supplier:Company {type: 'supplier'})
  -[:SUPPLIES*]->
  (manufacturer:Company {type: 'manufacturer'})
  -[:SHIPS_TO]->
  (distributor:Company {type: 'distributor'})
RETURN
  supplier.name,
  manufacturer.name,
  distributor.name,
  length(path) AS supply_chain_length,
  null /* reduce() is advertised by SHOW FUNCTIONS but has no evaluator dispatch; collect() the values and fold client-side (for path weights use algo.dijkstra.stream, which yields cost) */ AS total_lead_time
ORDER BY total_lead_time DESC;

-- Create indexes for analytical queries
CREATE INDEX user_timestamp ON User(last_active);
CREATE INDEX product_category_price ON Product(category, price);
CREATE INDEX order_customer_date ON Order(customer_id, order_date);

-- Profile analytical query
PROFILE
MATCH (u:User)-[:PURCHASED]->(p:Product)
WHERE p.category = 'Electronics'
  AND u.country = 'USA'
RETURN
  p.subcategory,
  count(u) AS customers,
  sum(p.price) AS revenue
GROUP BY p.subcategory;

-- Pre-compute expensive analytics
-- Materialized views are not reachable from GQL: the grammar has no
-- MATERIALIZED keyword. Precompute the aggregate onto nodes instead:
MATCH (u:User)-[:PURCHASED]->(p:Product)
WITH p, count(*) AS purchases
SET p.purchase_count = purchases;

-- Use materialized view
MATCH (s:daily_sales_summary)
WHERE s.region = 'West' AND s.day >= DATE '2024-01-01'
RETURN s.day, s.revenue
ORDER BY s.day;

-- Seed a tiny dataset
CREATE (:User {name: 'Alice'})-[:PURCHASED]->(:Product {category: 'Books'});

-- Run basic analytics
MATCH (u:User)-[:PURCHASED]->(p:Product)
RETURN p.category, count(u) AS customers
ORDER BY customers DESC;

-- Try path analysis
MATCH path = SHORTEST (a:User)-[:KNOWS*]-(b:User)
WHERE a.name = 'Alice' AND b.name = 'Bob'
RETURN path;