Question 212162 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql query for finding the products that had less than 10 sales and at least 1 sale within 7 days after their release

here are my tables:

  1. products table:
CREATE TABLE `products` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `price` decimal(15,2) NOT NULL,
  `total_profit` decimal(15,2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  1. orders table:
CREATE TABLE `orders` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  1. order_details table:
CREATE TABLE `order_details` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `order_id` bigint(20) UNSIGNED NOT NULL,
  `product_id` bigint(20) UNSIGNED NOT NULL,
  `quantity` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

what i want is a query that gets the ID of the products that had less than 10 sales and at least 1 sale within 7 days after their release, in ascending order of the product ID.

I’m trying this code. and it only return the first row of the joined table. i think it dosen’t count correctly!

SELECT p.id
FROM orders o
JOIN order_details od ON o.id = od.order_id
JOIN products p ON od.product_id = p.id
where timestampdiff(day, o.created_at, p.created_at) <= 7
having count(od.quantity)  between 0 and 10
order BY p.id asc;

here is sample data:

INSERT INTO `products` (`id`, `name`, `description`, `price`, `total_profit`, `created_at`) VALUES
(1, 'consequatur dolores dolorum', 'Amet iste.', '10899010.00', '0.00', '2022-04-05 18:47:19'),
(2, 'est dolor dolores', 'Minus.', '36014000.00', '0.00', '2022-04-06 18:47:19'),
(3, 'quibusdam sed vel', 'A quo sed.', '32255000.00', '0.00', '2022-04-07 18:47:19'),
(4, 'perferendis dolores molestias', 'Ipsam sit.', '10182000.00', '0.00', '2022-04-08 18:47:19'),
(5, 'fuga aspernatur natus', 'Earum quas.', '737000.00', '0.00', '2022-04-09 18:47:19'),
(6, 'voluptatibus incidunt nostrum', 'Quia possimus.', '36728000.00', '0.00', '2022-04-10 18:47:19'),
(7, 'et necessitatibus architecto', 'Aut.', '43860000.00', '0.00', '2022-04-11 18:47:19'),
(8, 'et id nisi', 'Qui id totam.', '10380000.00', '0.00', '2022-04-12 18:47:19'),
(9, 'ipsam ut iusto', 'Iusto.', '21780000.00', '0.00', '2022-04-13 18:47:19'),
(10, 'similique accusantium et', 'A qui ducimus.', '23156000.00', '0.00', '2022-04-14 18:47:19'),

INSERT INTO `order_details` (`id`, `order_id`, `product_id`, `quantity`) VALUES
(1, 2, 4, 8),
(2, 10, 49, 3),
(3, 6, 12, 8),
(4, 8, 3, 9),
(5, 9, 75, 7),
(6, 5, 4, 10),
(7, 7, 100, 7),
(8, 2, 63, 1),
(9, 1, 21, 2),
(10, 1, 79, 9),
(11, 4, 10, 6),

INSERT INTO `orders` (`id`, `user_id`, `created_at`) VALUES
(1, 217003, '2022-07-09 18:47:19'),
(2, 732493, '2022-07-10 06:47:19'),
(3, 269127, '2022-07-10 18:47:19'),
(4, 47245, '2022-07-11 06:47:19'),
(5, 207792, '2022-07-11 18:47:19'),
(6, 456178, '2022-07-12 06:47:19'),
(7, 875098, '2022-07-12 18:47:19'),
(8, 353625, '2022-07-13 06:47:19'),
(9, 258266, '2022-07-13 18:47:19'),
(10, 474065, '2022-07-14 06:47:19');
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I changed @Asgar 's answer a little and it was the key! here is the final asnwer:

    SELECT 
p.`id`
FROM orders o
JOIN order_details od ON o.`id` = od.`order_id`
JOIN `products` p ON od.`product_id` = p.`id`
WHERE TIMESTAMPDIFF(DAY, p.created_at, o.created_at) <= 7
GROUP BY p.`id`
HAVING sum(od.quantity) between 1 and 9
order by p.id asc

    I also thank @Barmar.


  2. 0

    I think you are just missing a GROUP BY:

    SELECT 
p.`id`, 
SUM(od.`quantity`) totalSold 
FROM orders o
JOIN order_details od ON o.`id`=od.`order_id`
JOIN `products` p ON od.`product_id`=p.`id`
WHERE TIMESTAMPDIFF(DAY, o.created_at, p.created_at) <= 7
GROUP BY p.`id`
HAVING totalSold<10

    And also your condition is to check LESS THAN 10, I do not understand why you wrote 0 AND 100

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search