Should I use looping or any other alternative for the mentioned example in MySQL

Question

Example Table 1

Example Table 2

Example Table 3

We currently have a query that is grouped by customer_id and a customer may have multiple booking_id's and each booking_id has its own tracker which can be Facebook install, Instagram install or Organic We need to create a new column as the expected output, which checks that if all the trackers are organic for each booking of a customer, then each row in the expected output would also be organic for that customer. But if a booking's tracker consists of facebook install or Instagram install and a booking after that is Organic for the same customer like in example Table 1 then the expected output should be the bookings that are Facebook and Instagram installs should remain the same but the booking that is organic should be assigned the name of the tracker of the booking above it which is Instagram Install as in the example table 1.

A possible solution without a procedure.

question from:https://stackoverflow.com/questions/65869475/should-i-use-looping-or-any-other-alternative-for-the-mentioned-example-in-mysql

Answer 1

Using coalesce and sub queries to get previous or next non organic

DROP TABLE IF EXISTS T;
CREATE TABLE T
(ID INT AUTO_INCREMENT PRIMARY KEY,CID INT,TRACKER VARCHAR(20));

INSERT INTO T(CID,TRACKER) VALUES
(1,'F'),(1,'I'),(1,'O'),
(2,'O'),(2,'F'),(2,'I'),
(3,'O'),(4,'F'),
(5,'F'),(5,'I'),(5,'O'),(5,'I'),(5,'F'),(5,'O'),(5,'F');


SELECT T.*,
       CASE WHEN T.TRACKER  = 'O' THEN
        COALESCE(
          (SELECT C.TRACKER FROM T C WHERE C.CID = T.CID AND C.ID < T.ID ORDER BY C.CID,C.ID DESC LIMIT 1),
          (SELECT C.TRACKER FROM T C WHERE C.CID = T.CID AND C.ID > T.ID ORDER BY C.CID,C.ID LIMIT 1),
          T.TRACKER
          )
          ELSE T.TRACKER
          END TRACKER
FROM T 
ORDER BY CID,ID;

+----+------+---------+---------+
| ID | CID  | TRACKER | TRACKER |
+----+------+---------+---------+
|  1 |    1 | F       | F       |
|  2 |    1 | I       | I       |
|  3 |    1 | O       | I       |
|  4 |    2 | O       | F       |
|  5 |    2 | F       | F       |
|  6 |    2 | I       | I       |
|  7 |    3 | O       | O       |
|  8 |    4 | F       | F       |
|  9 |    5 | F       | F       |
| 10 |    5 | I       | I       |
| 11 |    5 | O       | I       |
| 12 |    5 | I       | I       |
| 13 |    5 | F       | F       |
| 14 |    5 | O       | F       |
| 15 |    5 | F       | F       |
+----+------+---------+---------+
15 rows in set (0.002 sec)