BZN4WeMP

1-- The primary transactions table. This is partitioned by subscriber and clustered by transaction ID. The idea here is that we may
2-- have callbacks to update a state (i.e., "pending" vs "success" vs "failed") -- I'm not sure yet if this is a necessity, but I can
3-- imagine that this may be the case.
4--
5CREATE TABLE IF NOT EXISTS reward.transactions_by_subscriber_and_id (
6  operator_name text,
7  subscriber_id text,
8  transaction_id text,
9  operator_local_day text,              -- operator:local_day_timestamp (for secondary index)
10  operator_local_day_and_rule_id text,  -- operator:local_day_timestamp:rule_id (for secondary index)
11  absolute_time bigint,                 -- UTC timestamp
12  rule_id text,
13  state int,
14  misc text,
15  PRIMARY KEY ((operator_name, subscriber_id), transaction_id));
16 -- Query: SELECT * FROM reward.transactions_by_subscriber_and_id WHERE operator_name = 'indosat' AND subscriber_id = 'sub_id1';
17
18-- Used to look up subscriber history for CSRs and apps. We can't really use a secondary index here since range
19-- queries are not allowed on secondary indices (we'd need to construct a query for each day and issue them
20-- independently).
21--
22CREATE MATERIALIZED VIEW IF NOT EXISTS reward.transactions_by_subscriber_and_time
23AS
24  SELECT operator_name, subscriber_id, absolute_time, transaction_id, rule_id, state, misc
25  FROM reward.transactions_by_subscriber_and_id
26  WHERE operator_name IS NOT NULL
27    AND subscriber_id IS NOT NULL
28    AND absolute_time IS NOT NULL
29    AND transaction_id IS NOT NULL
30  PRIMARY KEY ((operator_name, subscriber_id), absolute_time, transaction_id)
31  WITH CLUSTERING ORDER BY (absolute_time DESC);
32-- Query: SELECT * FROM reward.transactions_by_subscriber_and_id WHERE operator_name = 'indosat' AND subscriber_id = 'sub_id1' AND absolute_time > 1524612548;
33
34-- To generate daily/monthly/yearly transaction reports, we can use a secondary index to retrieve data for particular days. This
35-- is relatively efficient as the data is located throughout the cluster, so there is no wasted work per node.
36--
37CREATE INDEX IF NOT EXISTS transactions_by_local_day ON reward.transactions_by_subscriber_and_id (operator_local_day);
38-- Query: SELECT * FROM reward.transactions_by_subscriber_and_id WHERE operator_local_day = 'indosat:1524612548';
39
40-- To generate daily/monthly/yearly transaction reports by rule ID.
41--
42CREATE INDEX IF NOT EXISTS transactions_by_local_day_and_rule_id ON reward.transactions_by_subscriber_and_id (operator_local_day_and_rule_id);
43-- Query: SELECT * FROM reward.transactions_by_subscriber_and_id WHERE operator_local_day_and_rule_id = 'indosat:1524612548:id1';