I have a use case to store number of SMSes, number of Calls, total call duration etc. made for particular phone numbers.
Later I want to make queries such as,
SELECT phone_number FROM <> WHERE (no_of_sms>5 and no_of_calls>5) OR (total_call_duration>30)
Our first approach was to define a keyspace which has column families dynamically created and indexed for each query having columns such as Phone_number, No_of_SMS, No_of_Calls etc. We tried to use counter columns, but failed as it doesn't support indexing. Then we went for standard columns, did the counting manually, querying existing value and updating it. But again it didn't support conditional queries as > and <.
Is Cassandra not supporting this kind of queries or is this a fault in data modeling? I am from a relational database background and totally new to this model. Going through Twissandra example I feel using the No_of SMSes as the key is a solution here.
Can someone give some guidance or hints on how should this be modeled to enable the query pattern or is this impossible?
Thanks in advance!