100 Interview Questions

• Partition key: user_id (distribution)
• Clustering keys: timestamp, event_type (sorting)

• PRIMARY KEY (user_id, order_id)
• All orders for a user stored in one partition

• Table 1: users_by_group - PRIMARY KEY (group_id, user_id)
• Table 2: groups_by_user - PRIMARY KEY (user_id, group_id)

• "Get all users in group X" → Query Table 1
• "Get all groups for user Y" → Query Table 2

• Slow queries (must read entire partition)
• Memory pressure (loaded into memory for queries)
• Compaction issues (huge compactions)
• Repair problems (streaming large partitions)

• Without bucket: All sensor data in one partition (grows forever)
• With bucket: One partition per sensor per day (bounded size)

CREATE TYPE address (
  street text,
  city text,
  zip int
);

CREATE TABLE users (
  id uuid PRIMARY KEY,
  name text,
  home_address frozen<address>
);

CREATE TABLE page_views (
  page_id text PRIMARY KEY,
  view_count counter
);
UPDATE page_views SET view_count = view_count + 1 WHERE page_id = 'home';

CREATE MATERIALIZED VIEW users_by_email AS
  SELECT * FROM users
  WHERE email IS NOT NULL
  PRIMARY KEY (email, user_id);