CREATE TABLE `transactions` (
  `record_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `trasaction_time` timestamp NOT NULL DEFAULT current_timestamp(),
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `transactions`
--

INSERT INTO `transactions` (`record_id`, `user_id`, `trasaction_time`, `status`) VALUES
(1, 1, '2021-01-01 06:33:17', 1),
(2, 1, '2021-01-01 18:03:17', 2),
(3, 2, '2021-01-01 06:04:32', 1),
(4, 2, '2021-01-01 18:04:10', 2),
(5, 1, '2021-01-02 06:28:20', 1),
(6, 1, '2021-01-02 17:58:29', 2),
(7, 2, '2021-01-02 06:40:42', 1),
(8, 2, '2021-01-02 18:40:42', 2),
(9, 1, '2021-01-03 06:02:11', 1),
(10, 1, '2021-01-03 18:41:12', 2),
(11, 2, '2021-01-03 05:55:33', 1),
(12, 2, '2021-01-03 17:45:33', 2);

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `login` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `first_name` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `status` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `login`, `first_name`, `status`) VALUES
(1, 'algol', 'robert', 1),
(2, 'Atom', 'Tormast', 1);

SELECT t1.record_id, t1.login, t1.first_name, t1.last_name, t1.status,
       DATE(t2.trasaction_time) `date`,
       MIN(CASE WHEN t2.status = 1 THEN t2.trasaction_time END) income,
       MAX(CASE WHEN t2.status = 2 THEN t2.trasaction_time END) outcome
FROM users t1
JOIN transactions t2 ON t1.record_id = t2.user_id
GROUP BY record_id, login, first_name, last_name, status, `date`
ORDER BY record_id, `date`;

SELECT t1.record_id, t1.login, t1.first_name, t1.last_name, t1.status,
       DATE(t2.trasaction_time) `date`,
       MIN(CASE WHEN t2.status = 1 THEN t2.trasaction_time END) income,
       MAX(CASE WHEN t2.status = 2 THEN t2.trasaction_time END) outcome,
       ROUND(TIME_TO_SEC(timediff(income, outcome))/60) AS diff
FROM users t1
JOIN transactions t2 ON t1.record_id = t2.user_id
GROUP BY record_id, login, first_name, last_name, status, `date`
ORDER BY record_id, `date`;