Hi I am gonna make an analytics on users’ behavior. Currently I have a table containing records of visits in my site and have another table containing records of a specific user behavior (e.g., leave a message in my site). Now I wanna know the behavior rate change based on a fixed interval (e.g., one day).
The process is generally as follows:
- group the visit records by date with interval of one day and calculate the counts of records by aggregation
- group the message records by date with the same interval and calculate the counts as well
- join the two aggregation result tables and filter out any joint records that dates do not match
- calculate the ratio of the two counts in each valid entry in the joint table
The process is much easier to implement by sql, but I have no idea how to implement it based on the bubble database system.
I will appreciate any help or comments. Thanks in advance!