MySQL HABTM - Find most consecutive widgets per user

Question

I currently have the following SQL database:

CREATE TABLE `users` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(40),
  `email` VARCHAR(40),
  PRIMARY KEY  (`id`)
);

CREATE TABLE `widgets` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `production_date` DATETIME,
  `title` VARCHAR(100),
  PRIMARY KEY  (`id`)
);

CREATE TABLE `users_widgets` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11),
  `widget_id` INT(11),
  PRIMARY KEY  (`id`)
);
ALTER TABLE `users_widgets` ADD CONSTRAINT `users_widgets_fk1` FOREIGN KEY (`user_id`) REFERENCES users(`id`);
ALTER TABLE `users_widgets` ADD CONSTRAINT `users_widgets_fk2` FOREIGN KEY (`widget_id`) REFERENCES widgets(`id`);

Users HABTM widgets. Basically, in this scenario, I need to figure out, for each user, how many widgets in a row that user paid for. Widgets should be ordered by production_date in order to figure out whether or not they are "consecutive" and widgets are not produced every day, but a widget produced on Feb 3d and a widget produced on Feb 11th could be consecutive, as long as no widgets were produced Feb 4-10.

This is how the site works: The site owner places a number of widgets up on the site, and users can pledge money towards the widget. The amount they pay will always be $1. Once enough users have paid for a widget, the widget is produced and a production_date is added to the widget.

What I am trying to implement is essentially a customer loyalty program. How many consecutive widgets that were put into production did the user buy? Then I can, say, offer a coupon or a discount for users that bought X widgets in a row. Each user can only pay for a widget once, but can pay for multiple widgets, some in production and some not. Widgets that are not in production (production_date=null) should not affect the calculation of consecutive widgets.

Auto-increment doesn't really work here, because widgets may be put into production in a different order than the database record was initially created. So, I need to order the widgets by production_date in order to determine which widgets are consecutive.

Answer 1

First of all - you need to specify what "paid for" means, and how this should be represented in your tables. Also, you need to clarify how you should handle the case where user added 2 paid widgets, a free widget and then additional 2 paid widgets.

As it seems to me that you are missing information in your users_widgets table, because your widgets can be assigned to many users, but you don't have the information of the date the widget was assigned to a specific user, and rather the widget was paid or not. I suggest that this table should look like:

CREATE TABLE `users_widgets` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11),
  `widget_id` INT(11),
  `purchase_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `price` decimal(10,2),
  PRIMARY KEY  (`id`)
);

Side notes:

1. If your widgets can't be assigned to several users - there is no point to split the data into 2 tables (widgets and users_widgest). In this case, keep only 1 table with all the data.

2. You can trust the autoincrement for order of the purchase events, but if you will not add the date of purchase - it will be lost.

3. If all of your widgets are paid - u can skip the price or paid field and perform a simple count of all user widgets.

Having the users_widgets table as I specified above, you should perform the following query:

SELECT a.user_id, sum(if(a.price is not null and b.price is not null,1,0)) 
FROM users_widgets a 
JOIN users_widgets b 
    ON a.user_id = b.user_id AND a.id<b.id 
LEFT OUTER JOIN users_widgets c 
    ON a.user_id = c.user_id AND a.id < c.id AND b.id > c.id 
WHERE c.id is null 
GROUP BY (a.user_id);

This query will tell you how many consecutive paid widgets were added per user. but please note, that if the user added 2 paid widgets, 1 free widgets and then again 2 paid widgets u will receive for that user result of 2 because only 2 purchases followed another purchase.

==========UPDATE==========

Check if this can help you to solve the problem

SELECT u1.user_id, count(1) 
FROM users_widgets u1 
JOIN widgets w1 
   ON u1.widget_id=w1.id 
JOIN widgets w2 
   ON w1.production_date<w2.production_date 
JOIN users_widgets u2 
   ON w2.id=u2.widget_id 
LEFT OUTER JOIN widgets w3 
   ON w1.production_date < w3.production_date 
   AND w2.production_date>w3.production_date 
WHERE w3.id is null 
  AND u1.user_id=u2.user_id 
GROUP BY user_id;

Please note that in this case still if the user bought 2 consecutive widgets then missed one and later on bought 2 additional - u will receive a count of 2 for this user (1 for every time he performed a consecutive purchase)

I strongly recommend you not to run such queries in your production Database.