I’m writing some mysql queries for a reporting application and need some help in creating five different main queries.
It may be a combination of mysql with php to get the exact results.
I have a table with one transaction per row … it includes user_id, transaction id, date, product (and other fields which are unrelated)
The queries I need to create are:
(1) how many results for a particular month for new users (i.e. this is the first occurrence of that user_id)
(2) how many results for a particular month where there was no transaction for a user_id that, no transactions in the next two months, but there was in a previous month
(3) how many transactions for a particular month where the user has a transaction this month and the product ID for the current month is a “higher level” we have a separate table to determine levels) than in previous months
(4) how many transactions for a particular month where the user has a transaction this month and the product ID for the current month is a “lower level” than previous months
(5) how many transactions for a particular month where the user has a transaction this month, did not have one the previous two months, but has one at the same level prior to that 3 months ago.
