MariaDB 10.2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(11) NOT NULL,
  `uid` int(11) DEFAULT NULL,
  `lid` int(11) DEFAULT NULL,
  `amount_type` varchar(200) DEFAULT NULL,
  `amount_value` decimal(12,2) DEFAULT '0.00',
  `automated_payment` tinyint(1) DEFAULT NULL,
  `status` varchar(100) DEFAULT NULL,
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `used` tinyint(4) DEFAULT '0',
  `ref_proc_t` tinyint(4) DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=utf8;
INSERT INTO `orders` (`id`, `uid`, `lid`, `amount_type`, `amount_value`, `automated_payment`, `status`, `create_date`, `used`, `ref_proc_t`) VALUES
(14, 14, 8, 'RUB', 300000.00, 1, 'Completed', '2019-08-04 05:09:23', 1, 1),
(17, 33, 1, 'RUB', 10000.00, 1, 'Completed', '2019-08-04 05:50:57', 1, 1),
(18, 34, 1, 'RUB', 10000.00, 1, 'Completed', '2019-08-04 05:53:32', 1, 1),
(19, 35, 2, 'RUB', 100000.00, 1, 'Completed', '2019-08-09 23:50:56', 1, 1),
(21, 36, 2, 'RUB', 100000.00, 1, 'Completed', '2019-08-10 00:07:17', 1, 1),
(22, 32, 2, 'RUB', 100000.00, 1, 'Completed', '2019-08-10 00:10:47', 1, 1),
(23, 37, 4, 'RUB', 20000.00, 1, 'Completed', '2019-08-18 23:17:16', 1, 1),
(24, 45, 1, 'RUB', 10000.00, 1, 'Completed', '2019-09-02 00:28:30', 1, 1),
(25, 46, 1, 'RUB', 10000.00, 1, 'Completed', '2019-09-02 00:32:32', 1, 1),
(35, 55, 2, 'RUB', 100000.00, 1, 'Completed', '2019-11-15 17:39:25', 1, 1),
(39, 56, 4, 'RUB', 20000.00, 1, 'Completed', '2019-11-15 18:56:06', 1, 1),
(40, 57, 5, 'RUB', 2000000.00, 1, 'Completed', '2019-11-15 19:01:44', 1, 1),
(41, 58, 1, 'RUB', 10000.00, 1, 'Completed', '2019-11-15 19:33:49', 1, 1),
(42, 60, 3, 'RUB', 1000000.00, 1, 'Completed', '2019-09-01 09:19:15', 1, 1),
(43, 61, 1, 'RUB', 10000.00, 1, 'Completed', '2019-11-15 23:46:06', 1, 1),
(44, 62, 2, 'RUB', 100000.00, 1, 'Completed', '2019-11-16 01:10:01', 1, 1),
(45, 63, 13, 'RUB', 40000.00, 1, 'Completed', '2019-10-15 07:29:19', 1, 1),
(46, 64, 4, 'RUB', 20000.00, 1, 'Completed', '2019-11-16 07:51:57', 1, 1),
(47, 65, 2, 'RUB', 100000.00, 1, 'Completed', '2019-11-16 10:08:11', 1, 1),
(48, 64, 1, 'RUB', 10000.00, 1, 'Completed', '2019-11-16 10:24:28', 1, 1),
(49, 66, 2, 'RUB', 100000.00, 1, 'Completed', '2019-11-16 23:00:31', 1, 1),
(51, 68, 2, 'RUB', 100000.00, 1, 'Completed', '2019-11-16 23:07:39', 1, 1),
(52, 69, 3, 'RUB', 1000000.00, 1, 'Completed', '2019-09-01 11:24:32', 1, 1),
(53, 70, 2, 'RUB', 100000.00, 1, 'Completed', '2019-11-16 23:18:21', 1, 1),
(54, 71, 4, 'RUB', 20000.00, 1, 'Completed', '2019-11-16 23:26:37', 1, 1),
(56, 73, 2, 'RUB', 100000.00, 1, 'Completed', '2019-11-16 23:35:04', 1, 1),
(57, 74, 9, 'RUB', 400000.00, 1, 'Completed', '2019-11-16 23:39:39', 1, 1),
(58, 75, 4, 'RUB', 20000.00, 1, 'Completed', '2019-11-16 23:46:19', 1, 1),
(59, 76, 1, 'RUB', 10000.00, 1, 'Completed', '2019-11-16 23:51:29', 1, 1),
(61, 78, 13, 'RUB', 40000.00, 1, 'Completed', '2019-11-16 23:57:29', 1, 1),
(62, 79, 2, 'RUB', 100000.00, 1, 'Completed', '2019-11-17 00:47:16', 1, 1),
(65, 82, 5, 'RUB', 30000.00, 1, 'Completed', '2019-11-17 00:54:42', 1, 1),
(68, 83, 1, 'RUB', 10000.00, 1, 'Completed', '2019-11-17 13:15:42', 1, 1),
(69, 84, 1, 'RUB', 10000.00, 1, 'pending', '2019-11-17 22:27:56', 0, 0),
(70, 87, 1, 'RUB', 10000.00, 1, 'Completed', '2019-11-21 20:58:57', 1, 1),
(74, 91, 2, 'RUB', 100000.00, 1, 'Completed', '2019-11-22 21:01:17', 0, 0),
(75, 92, 5, 'RUB', 30000.00, 1, 'Completed', '2019-11-25 18:54:02', 1, 1),
(76, 93, 1, 'RUB', 10000.00, 1, 'pending', '2019-11-26 15:19:57', 0, 0),
(77, 82, 1, 'RUB', 10000.00, 1, 'pending', '2019-11-27 18:45:09', 0, 0),
(85, 95, 5, 'RUB', 30000.00, 1, 'Completed', '2019-11-30 09:56:50', 1, 1),
(87, 101, 2, 'RUB', 100000.00, 1, 'Completed', '2019-12-02 17:39:03', 1, 0),
(88, 103, 3, 'RUB', 1000000.00, 1, 'Completed', '2019-12-20 18:14:35', 1, 0),
(89, 82, 2, 'RUB', 100000.00, 1, 'pending', '2019-12-23 21:39:49', 0, 0),
(90, 82, 9, 'RUB', 400000.00, 1, 'pending', '2019-12-23 21:48:30', 0, 0),
(91, 104, 1, 'RUB', 10000.00, 1, 'Completed', '2020-01-06 20:26:17', 1, 0),
(92, 82, 2, 'RUB', 100000.00, 1, 'pending', '2020-01-22 10:34:39', 0, 0),
(93, 82, 2, 'RUB', 100000.00, 1, 'pending', '2020-01-22 10:35:11', 0, 0),
(94, 105, 1, 'RUB', 10000.00, 1, 'Completed', '2020-02-09 18:43:47', 1, 0),
(95, 106, 1, 'RUB', 10000.00, 1, 'Completed', '2020-02-09 20:55:53', 1, 0),
(96, 107, 2, 'RUB', 100000.00, 1, 'Completed', '2020-02-09 20:57:20', 1, 0),
(97, 95, 2, 'RUB', 100000.00, 1, 'pending', '2020-02-15 22:34:09', 0, 0);
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `orders`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=98;
SELECT * FROM orders;


id
uid
lid
amount_type
amount_value
automated_payment
status
create_date
used
ref_proc_t


14
14
8
RUB
300000.00
1
Completed
2019-08-04 05:09:23
1
1

17
33
1
RUB
10000.00
1
Completed
2019-08-04 05:50:57
1
1

18
34
1
RUB
10000.00
1
Completed
2019-08-04 05:53:32
1
1

19
35
2
RUB
100000.00
1
Completed
2019-08-09 23:50:56
1
1

21
36
2
RUB
100000.00
1
Completed
2019-08-10 00:07:17
1
1

22
32
2
RUB
100000.00
1
Completed
2019-08-10 00:10:47
1
1

23
37
4
RUB
20000.00
1
Completed
2019-08-18 23:17:16
1
1

24
45
1
RUB
10000.00
1
Completed
2019-09-02 00:28:30
1
1

25
46
1
RUB
10000.00
1
Completed
2019-09-02 00:32:32
1
1

35
55
2
RUB
100000.00
1
Completed
2019-11-15 17:39:25
1
1

39
56
4
RUB
20000.00
1
Completed
2019-11-15 18:56:06
1
1

40
57
5
RUB
2000000.00
1
Completed
2019-11-15 19:01:44
1
1

41
58
1
RUB
10000.00
1
Completed
2019-11-15 19:33:49
1
1

42
60
3
RUB
1000000.00
1
Completed
2019-09-01 09:19:15
1
1

43
61
1
RUB
10000.00
1
Completed
2019-11-15 23:46:06
1
1

44
62
2
RUB
100000.00
1
Completed
2019-11-16 01:10:01
1
1

45
63
13
RUB
40000.00
1
Completed
2019-10-15 07:29:19
1
1

46
64
4
RUB
20000.00
1
Completed
2019-11-16 07:51:57
1
1

47
65
2
RUB
100000.00
1
Completed
2019-11-16 10:08:11
1
1

48
64
1
RUB
10000.00
1
Completed
2019-11-16 10:24:28
1
1

49
66
2
RUB
100000.00
1
Completed
2019-11-16 23:00:31
1
1

51
68
2
RUB
100000.00
1
Completed
2019-11-16 23:07:39
1
1

52
69
3
RUB
1000000.00
1
Completed
2019-09-01 11:24:32
1
1

53
70
2
RUB
100000.00
1
Completed
2019-11-16 23:18:21
1
1

54
71
4
RUB
20000.00
1
Completed
2019-11-16 23:26:37
1
1

56
73
2
RUB
100000.00
1
Completed
2019-11-16 23:35:04
1
1

57
74
9
RUB
400000.00
1
Completed
2019-11-16 23:39:39
1
1

58
75
4
RUB
20000.00
1
Completed
2019-11-16 23:46:19
1
1

59
76
1
RUB
10000.00
1
Completed
2019-11-16 23:51:29
1
1

61
78
13
RUB
40000.00
1
Completed
2019-11-16 23:57:29
1
1

62
79
2
RUB
100000.00
1
Completed
2019-11-17 00:47:16
1
1

65
82
5
RUB
30000.00
1
Completed
2019-11-17 00:54:42
1
1

68
83
1
RUB
10000.00
1
Completed
2019-11-17 13:15:42
1
1

69
84
1
RUB
10000.00
1
pending
2019-11-17 22:27:56
0
0

70
87
1
RUB
10000.00
1
Completed
2019-11-21 20:58:57
1
1

74
91
2
RUB
100000.00
1
Completed
2019-11-22 21:01:17
0
0

75
92
5
RUB
30000.00
1
Completed
2019-11-25 18:54:02
1
1

76
93
1
RUB
10000.00
1
pending
2019-11-26 15:19:57
0
0

77
82
1
RUB
10000.00
1
pending
2019-11-27 18:45:09
0
0

85
95
5
RUB
30000.00
1
Completed
2019-11-30 09:56:50
1
1

87
101
2
RUB
100000.00
1
Completed
2019-12-02 17:39:03
1
0

88
103
3
RUB
1000000.00
1
Completed
2019-12-20 18:14:35
1
0

89
82
2
RUB
100000.00
1
pending
2019-12-23 21:39:49
0
0

90
82
9
RUB
400000.00
1
pending
2019-12-23 21:48:30
0
0

91
104
1
RUB
10000.00
1
Completed
2020-01-06 20:26:17
1
0

92
82
2
RUB
100000.00
1
pending
2020-01-22 10:34:39
0
0

93
82
2
RUB
100000.00
1
pending
2020-01-22 10:35:11
0
0

94
105
1
RUB
10000.00
1
Completed
2020-02-09 18:43:47
1
0

95
106
1
RUB
10000.00
1
Completed
2020-02-09 20:55:53
1
0

96
107
2
RUB
100000.00
1
Completed
2020-02-09 20:57:20
1
0

97
95
2
RUB
100000.00
1
pending
2020-02-15 22:34:09
0
0


CREATE TABLE IF NOT EXISTS `u_af` (
  `id` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `rank_id` int(11) NOT NULL,
  `start_data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` tinyint(1) DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=utf8;
INSERT INTO `u_af` (`id`, `uid`, `rank_id`, `start_data`, `status`) VALUES
(12, 14, 4, '2019-08-01 22:04:24', 1),
(29, 32, 4, '2019-08-04 05:48:28', 1),
(30, 33, 4, '2019-08-04 05:50:57', 1),
(31, 34, 4, '2019-08-04 05:53:32', 1),
(32, 35, 4, '2019-08-09 23:50:55', 1),
(33, 36, 4, '2019-08-10 00:02:12', 1),
(34, 37, 4, '2019-08-18 23:17:16', 1),
(39, 45, 6, '2019-09-02 00:28:30', 1),
(40, 46, 4, '2019-09-02 00:32:32', 1),
(47, 55, 4, '2019-11-15 17:39:25', 1),
(48, 56, 4, '2019-11-15 18:01:09', 1),
(49, 57, 4, '2019-11-15 19:01:43', 1),
(50, 58, 4, '2019-11-15 19:33:49', 1),
(51, 59, 4, '2019-11-15 20:56:36', 1),
(52, 60, 4, '2019-08-31 22:55:33', 1),
(53, 61, 4, '2019-11-15 23:46:06', 1),
(54, 62, 4, '2019-11-17 01:10:01', 1),
(55, 63, 4, '2019-10-15 07:29:19', 1),
(56, 64, 4, '2019-10-15 07:51:57', 1),
(57, 65, 4, '2019-11-16 10:08:11', 1),
(58, 66, 4, '2019-11-16 23:00:30', 1),
(60, 68, 4, '2019-11-16 23:07:39', 1),
(61, 69, 4, '2019-11-16 23:12:32', 1),
(62, 70, 4, '2019-11-16 23:18:21', 1),
(63, 71, 4, '2019-11-16 23:26:37', 1),
(65, 73, 4, '2019-11-16 23:35:04', 1),
(66, 74, 4, '2019-11-16 23:39:38', 1),
(67, 75, 4, '2019-11-16 23:46:19', 1),
(68, 76, 4, '2019-11-16 23:51:29', 1),
(70, 78, 4, '2019-11-16 23:57:29', 1),
(71, 79, 4, '2019-11-17 00:47:16', 1),
(74, 82, 4, '2019-11-17 00:54:42', 1),
(75, 83, 6, '2019-11-17 13:15:42', 1),
(79, 87, 6, '2019-11-21 21:02:11', 1),
(83, 91, 6, '2019-11-22 21:01:17', 1),
(85, 93, 6, '2019-11-26 15:19:57', 1),
(87, 95, 6, '2019-11-28 14:34:43', 1),
(91, 101, 6, '2019-12-02 17:41:11', 1),
(92, 103, 6, '2019-12-20 18:11:23', 1),
(93, 104, 6, '2020-01-06 20:31:54', 1),
(94, 105, 6, '2020-02-09 18:43:57', 1),
(95, 107, 6, '2020-02-09 20:57:45', 1),
(96, 106, 6, '2020-02-09 20:57:53', 1);
ALTER TABLE `u_af`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_u_af_uid` (`uid`);
ALTER TABLE `u_af`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=97;
SELECT * FROM u_af;


id
uid
rank_id
start_data
status


12
14
4
2019-08-01 22:04:24
1

29
32
4
2019-08-04 05:48:28
1

30
33
4
2019-08-04 05:50:57
1

31
34
4
2019-08-04 05:53:32
1

32
35
4
2019-08-09 23:50:55
1

33
36
4
2019-08-10 00:02:12
1

34
37
4
2019-08-18 23:17:16
1

39
45
6
2019-09-02 00:28:30
1

40
46
4
2019-09-02 00:32:32
1

47
55
4
2019-11-15 17:39:25
1

48
56
4
2019-11-15 18:01:09
1

49
57
4
2019-11-15 19:01:43
1

50
58
4
2019-11-15 19:33:49
1

51
59
4
2019-11-15 20:56:36
1

52
60
4
2019-08-31 22:55:33
1

53
61
4
2019-11-15 23:46:06
1

54
62
4
2019-11-17 01:10:01
1

55
63
4
2019-10-15 07:29:19
1

56
64
4
2019-10-15 07:51:57
1

57
65
4
2019-11-16 10:08:11
1

58
66
4
2019-11-16 23:00:30
1

60
68
4
2019-11-16 23:07:39
1

61
69
4
2019-11-16 23:12:32
1

62
70
4
2019-11-16 23:18:21
1

63
71
4
2019-11-16 23:26:37
1

65
73
4
2019-11-16 23:35:04
1

66
74
4
2019-11-16 23:39:38
1

67
75
4
2019-11-16 23:46:19
1

68
76
4
2019-11-16 23:51:29
1

70
78
4
2019-11-16 23:57:29
1

71
79
4
2019-11-17 00:47:16
1

74
82
4
2019-11-17 00:54:42
1

75
83
6
2019-11-17 13:15:42
1

79
87
6
2019-11-21 21:02:11
1

83
91
6
2019-11-22 21:01:17
1

85
93
6
2019-11-26 15:19:57
1

87
95
6
2019-11-28 14:34:43
1

91
101
6
2019-12-02 17:41:11
1

92
103
6
2019-12-20 18:11:23
1

93
104
6
2020-01-06 20:31:54
1

94
105
6
2020-02-09 18:43:57
1

95
107
6
2020-02-09 20:57:45
1

96
106
6
2020-02-09 20:57:53
1


CREATE TABLE IF NOT EXISTS `arur` (
  `id` int(11) NOT NULL,
  `af_id` int(11) NOT NULL,
  `r_w_uid` int(11) DEFAULT NULL,
  `DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=62 DEFAULT CHARSET=utf8;
INSERT INTO `arur` (`id`, `af_id`, `r_w_uid`, `DATE`) VALUES
(8, 12, 25, '2019-08-04 00:57:32'),
(11, 12, 28, '2019-08-04 04:50:30'),
(12, 12, 29, '2019-08-04 04:57:18'),
(13, 12, 30, '2019-08-04 05:01:37'),
(15, 12, 32, '2019-08-04 05:48:29'),
(16, 12, 33, '2019-08-04 05:50:57'),
(17, 30, 34, '2019-08-04 05:53:32'),
(18, 12, 35, '2019-08-09 23:50:56'),
(19, 30, 36, '2019-08-10 00:02:13'),
(20, 12, 37, '2019-08-18 23:17:16'),
(21, 29, 45, '2019-09-02 00:28:30'),
(22, 29, 46, '2019-09-02 00:32:32'),
(23, 47, 55, '2019-11-15 17:44:16'),
(24, 47, 56, '2019-11-15 18:01:09'),
(25, 47, 57, '2019-11-15 19:01:44'),
(26, 49, 58, '2019-11-15 19:33:49'),
(27, 49, 61, '2019-11-15 23:46:06'),
(28, 49, 62, '2019-11-16 01:10:01'),
(29, 49, 63, '2019-11-16 07:29:19'),
(30, 55, 64, '2019-11-16 07:51:57'),
(31, 52, 65, '2019-11-16 10:08:11'),
(32, 52, 66, '2019-11-16 23:00:31'),
(34, 52, 68, '2019-11-16 23:07:39'),
(35, 52, 69, '2019-11-16 23:12:32'),
(36, 52, 70, '2019-11-16 23:18:21'),
(37, 52, 71, '2019-11-16 23:26:37'),
(38, 63, 72, '2019-11-16 23:33:07'),
(39, 63, 73, '2019-11-16 23:35:04'),
(40, 63, 74, '2019-11-16 23:39:39'),
(41, 52, 75, '2019-11-16 23:46:19'),
(42, 52, 76, '2019-11-16 23:51:29'),
(43, 67, 77, '2019-11-16 23:55:52'),
(44, 67, 78, '2019-11-16 23:57:29'),
(45, 57, 79, '2019-11-17 00:47:16'),
(46, 57, 80, '2019-11-17 00:51:22'),
(47, 57, 81, '2019-11-17 00:53:05'),
(48, 74, 82, '2019-11-17 00:54:42'),
(49, 55, 83, '2019-11-17 13:15:42'),
(50, 58, 84, '2019-11-17 22:27:56'),
(51, 55, 88, '2019-11-21 22:43:37'),
(52, 55, 89, '2019-11-22 08:27:56'),
(53, 55, 90, '2019-11-22 09:09:19'),
(54, 52, 91, '2019-11-22 21:01:17'),
(55, 83, 93, '2019-11-26 15:19:57'),
(56, 74, 94, '2019-11-28 13:57:44'),
(57, 74, 95, '2019-11-28 14:34:43'),
(58, 47, 101, '2019-12-02 17:39:03'),
(59, 49, 104, '2020-01-06 20:26:17'),
(60, 39, 106, '2020-02-09 20:55:53'),
(61, 39, 107, '2020-02-09 20:57:20');
ALTER TABLE `arur`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_arur_af_id` (`af_id`),
  ADD KEY `idx_arur_r_w_uid` (`r_w_uid`);
ALTER TABLE `arur`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=62;
SELECT * FROM arur;


id
af_id
r_w_uid
DATE


8
12
25
2019-08-04 00:57:32

11
12
28
2019-08-04 04:50:30

12
12
29
2019-08-04 04:57:18

13
12
30
2019-08-04 05:01:37

15
12
32
2019-08-04 05:48:29

16
12
33
2019-08-04 05:50:57

17
30
34
2019-08-04 05:53:32

18
12
35
2019-08-09 23:50:56

19
30
36
2019-08-10 00:02:13

20
12
37
2019-08-18 23:17:16

21
29
45
2019-09-02 00:28:30

22
29
46
2019-09-02 00:32:32

23
47
55
2019-11-15 17:44:16

24
47
56
2019-11-15 18:01:09

25
47
57
2019-11-15 19:01:44

26
49
58
2019-11-15 19:33:49

27
49
61
2019-11-15 23:46:06

28
49
62
2019-11-16 01:10:01

29
49
63
2019-11-16 07:29:19

30
55
64
2019-11-16 07:51:57

31
52
65
2019-11-16 10:08:11

32
52
66
2019-11-16 23:00:31

34
52
68
2019-11-16 23:07:39

35
52
69
2019-11-16 23:12:32

36
52
70
2019-11-16 23:18:21

37
52
71
2019-11-16 23:26:37

38
63
72
2019-11-16 23:33:07

39
63
73
2019-11-16 23:35:04

40
63
74
2019-11-16 23:39:39

41
52
75
2019-11-16 23:46:19

42
52
76
2019-11-16 23:51:29

43
67
77
2019-11-16 23:55:52

44
67
78
2019-11-16 23:57:29

45
57
79
2019-11-17 00:47:16

46
57
80
2019-11-17 00:51:22

47
57
81
2019-11-17 00:53:05

48
74
82
2019-11-17 00:54:42

49
55
83
2019-11-17 13:15:42

50
58
84
2019-11-17 22:27:56

51
55
88
2019-11-21 22:43:37

52
55
89
2019-11-22 08:27:56

53
55
90
2019-11-22 09:09:19

54
52
91
2019-11-22 21:01:17

55
83
93
2019-11-26 15:19:57

56
74
94
2019-11-28 13:57:44

57
74
95
2019-11-28 14:34:43

58
47
101
2019-12-02 17:39:03

59
49
104
2020-01-06 20:26:17

60
39
106
2020-02-09 20:55:53

61
39
107
2020-02-09 20:57:20


    SELECT ua.id, io.uid,
            SUM(amount_value * 0.1) 
        FROM orders io 
        LEFT JOIN u_af ua ON io.uid = ua.uid 
        LEFT JOIN arur umr on umr.af_id = ua.id AND umr.r_w_uid = ua.id
            WHERE io.status ='Completed'   GROUP BY io.uid


id
uid
SUM(amount_value * 0.1)


12
14
30000.000

29
32
10000.000

30
33
1000.000

31
34
1000.000

32
35
10000.000

33
36
10000.000

34
37
2000.000

39
45
1000.000

40
46
1000.000

47
55
10000.000

48
56
2000.000

49
57
200000.000

50
58
1000.000

52
60
100000.000

53
61
1000.000

54
62
10000.000

55
63
4000.000

56
64
3000.000

57
65
10000.000

58
66
10000.000

60
68
10000.000

61
69
100000.000

62
70
10000.000

63
71
2000.000

65
73
10000.000

66
74
40000.000

67
75
2000.000

68
76
1000.000

70
78
4000.000

71
79
10000.000

74
82
3000.000

75
83
1000.000

79
87
1000.000

83
91
10000.000

null
92
3000.000

87
95
3000.000

91
101
10000.000

92
103
100000.000

93
104
1000.000

94
105
1000.000

96
106
1000.000

95
107
10000.000


-- запрос на получение `id` первоначальных платежей, с датами
SELECT o.id, o.uid, o.amount_value, o.create_date
FROM orders o
JOIN ( SELECT MIN(o2.create_date) create_date, o2.uid
       FROM orders o2
       WHERE o2.status = 'Completed'
       GROUP BY uid ) t3 USING (create_date, uid);


id
uid
amount_value
create_date


14
14
300000.00
2019-08-04 05:09:23

17
33
10000.00
2019-08-04 05:50:57

18
34
10000.00
2019-08-04 05:53:32

19
35
100000.00
2019-08-09 23:50:56

21
36
100000.00
2019-08-10 00:07:17

22
32
100000.00
2019-08-10 00:10:47

23
37
20000.00
2019-08-18 23:17:16

24
45
10000.00
2019-09-02 00:28:30

25
46
10000.00
2019-09-02 00:32:32

35
55
100000.00
2019-11-15 17:39:25

39
56
20000.00
2019-11-15 18:56:06

40
57
2000000.00
2019-11-15 19:01:44

41
58
10000.00
2019-11-15 19:33:49

42
60
1000000.00
2019-09-01 09:19:15

43
61
10000.00
2019-11-15 23:46:06

44
62
100000.00
2019-11-16 01:10:01

45
63
40000.00
2019-10-15 07:29:19

46
64
20000.00
2019-11-16 07:51:57

47
65
100000.00
2019-11-16 10:08:11

49
66
100000.00
2019-11-16 23:00:31

51
68
100000.00
2019-11-16 23:07:39

52
69
1000000.00
2019-09-01 11:24:32

53
70
100000.00
2019-11-16 23:18:21

54
71
20000.00
2019-11-16 23:26:37

56
73
100000.00
2019-11-16 23:35:04

57
74
400000.00
2019-11-16 23:39:39

58
75
20000.00
2019-11-16 23:46:19

59
76
10000.00
2019-11-16 23:51:29

61
78
40000.00
2019-11-16 23:57:29

62
79
100000.00
2019-11-17 00:47:16

65
82
30000.00
2019-11-17 00:54:42

68
83
10000.00
2019-11-17 13:15:42

70
87
10000.00
2019-11-21 20:58:57

74
91
100000.00
2019-11-22 21:01:17

75
92
30000.00
2019-11-25 18:54:02

85
95
30000.00
2019-11-30 09:56:50

87
101
100000.00
2019-12-02 17:39:03

88
103
1000000.00
2019-12-20 18:14:35

91
104
10000.00
2020-01-06 20:26:17

94
105
10000.00
2020-02-09 18:43:47

95
106
10000.00
2020-02-09 20:55:53

96
107
100000.00
2020-02-09 20:57:20


-- Запрос на получение отношений продавцов с клиентами
WITH 
cte1 AS (
SELECT t2.id prodavec, 
       t1.r_w_uid klient
FROM arur t1
JOIN u_af t2 ON t1.af_id = t2.id
)
SELECT prodavec, klient
FROM cte1
WHERE prodavec = 52
ORDER BY prodavec;


prodavec
klient


52
65

52
66

52
68

52
69

52
70

52
71

52
75

52
76

52
91


-- Запрос на получение отношений продавцов с клиентами + сколько каждый клиент заплатил
WITH 
cte1 AS (
SELECT t3.amount_value,
       t2.id af_id_prodavec, 
       t1.r_w_uid r_w_uid_klient
FROM arur t1
JOIN u_af t2 ON t1.af_id = t2.id
JOIN orders t3 ON t1.r_w_uid = t3.uid
)
SELECT af_id_prodavec, r_w_uid_klient, amount_value
FROM cte1
ORDER BY af_id_prodavec;


af_id_prodavec
r_w_uid_klient
amount_value


12
33
10000.00

12
35
100000.00

12
32
100000.00

12
37
20000.00

29
45
10000.00

29
46
10000.00

30
34
10000.00

30
36
100000.00

39
106
10000.00

39
107
100000.00

47
101
100000.00

47
55
100000.00

47
56
20000.00

47
57
2000000.00

49
62
100000.00

49
63
40000.00

49
104
10000.00

49
58
10000.00

49
61
10000.00

52
65
100000.00

52
91
100000.00

52
66
100000.00

52
68
100000.00

52
69
1000000.00

52
70
100000.00

52
71
20000.00

52
75
20000.00

52
76
10000.00

55
83
10000.00

55
64
20000.00

55
64
10000.00

57
79
100000.00

58
84
10000.00

63
73
100000.00

63
74
400000.00

67
78
40000.00

74
82
30000.00

74
82
10000.00

74
95
30000.00

74
82
100000.00

74
82
400000.00

74
82
100000.00

74
82
100000.00

74
95
100000.00

83
93
10000.00


-- Запрос на сумму и проценты для u_af.id = 52
WITH 
cte1 AS (
SELECT t3.amount_value,
       t2.id af_id_prodavec, 
       t1.r_w_uid klient
FROM arur t1
JOIN u_af t2 ON t1.af_id = t2.id
JOIN orders t3 ON t1.r_w_uid = t3.uid
)
SELECT af_id_prodavec, SUM(amount_value) AS summa, SUM(amount_value * 0.1) AS 10_procentov
FROM cte1
WHERE af_id_prodavec = 52
ORDER BY af_id_prodavec;


af_id_prodavec
summa
10_procentov


52
1550000.00
155000.000


select af_id, sum(amount_value)*0.1 sum_pair_10
from (
        select 
        arur.af_id,
        arur.r_w_uid,
        lag(arur.r_w_uid) over (partition by arur.af_id order by arur.r_w_uid) r_w_uid_1
        ,row_number() OVER (partition by arur.af_id order by arur.r_w_uid ) mod 2 pair_flag
        from arur) t1  left join orders o on (o.uid in (t1.r_w_uid,t1.r_w_uid_1) and o.status = 'Completed')
where pair_flag = 0
group by af_id
having count(pair_flag) >  5


af_id
sum_pair_10


12
23000.000

52
145000.000


select af_id, sum(coalesce(amount_value,0))*0.1 
from (
        select 
        arur.af_id,
        arur.r_w_uid,
        lag(arur.r_w_uid) over (partition by arur.af_id order by arur.r_w_uid) r_w_uid_1
        ,row_number() OVER (partition by arur.af_id order by arur.r_w_uid ) mod 2 pair_flag
        
        from arur, u_af 
        where u_af.id = arur.af_id ) t1  left join orders o on (o.uid in (t1.r_w_uid,t1.r_w_uid_1) and o.status = 'Completed')
where pair_flag = 0
group by af_id
having count(distinct r_w_uid) >  3


af_id
sum(coalesce(amount_value,0))*0.1


12
23000.000

52
145000.000

id	uid	lid	amount_type	amount_value	automated_payment	status	create_date	used	ref_proc_t
14	14	8	RUB	300000.00	1	Completed	2019-08-04 05:09:23	1	1
17	33	1	RUB	10000.00	1	Completed	2019-08-04 05:50:57	1	1
18	34	1	RUB	10000.00	1	Completed	2019-08-04 05:53:32	1	1
19	35	2	RUB	100000.00	1	Completed	2019-08-09 23:50:56	1	1
21	36	2	RUB	100000.00	1	Completed	2019-08-10 00:07:17	1	1
22	32	2	RUB	100000.00	1	Completed	2019-08-10 00:10:47	1	1
23	37	4	RUB	20000.00	1	Completed	2019-08-18 23:17:16	1	1
24	45	1	RUB	10000.00	1	Completed	2019-09-02 00:28:30	1	1
25	46	1	RUB	10000.00	1	Completed	2019-09-02 00:32:32	1	1
35	55	2	RUB	100000.00	1	Completed	2019-11-15 17:39:25	1	1
39	56	4	RUB	20000.00	1	Completed	2019-11-15 18:56:06	1	1
40	57	5	RUB	2000000.00	1	Completed	2019-11-15 19:01:44	1	1
41	58	1	RUB	10000.00	1	Completed	2019-11-15 19:33:49	1	1
42	60	3	RUB	1000000.00	1	Completed	2019-09-01 09:19:15	1	1
43	61	1	RUB	10000.00	1	Completed	2019-11-15 23:46:06	1	1
44	62	2	RUB	100000.00	1	Completed	2019-11-16 01:10:01	1	1
45	63	13	RUB	40000.00	1	Completed	2019-10-15 07:29:19	1	1
46	64	4	RUB	20000.00	1	Completed	2019-11-16 07:51:57	1	1
47	65	2	RUB	100000.00	1	Completed	2019-11-16 10:08:11	1	1
48	64	1	RUB	10000.00	1	Completed	2019-11-16 10:24:28	1	1
49	66	2	RUB	100000.00	1	Completed	2019-11-16 23:00:31	1	1
51	68	2	RUB	100000.00	1	Completed	2019-11-16 23:07:39	1	1
52	69	3	RUB	1000000.00	1	Completed	2019-09-01 11:24:32	1	1
53	70	2	RUB	100000.00	1	Completed	2019-11-16 23:18:21	1	1
54	71	4	RUB	20000.00	1	Completed	2019-11-16 23:26:37	1	1
56	73	2	RUB	100000.00	1	Completed	2019-11-16 23:35:04	1	1
57	74	9	RUB	400000.00	1	Completed	2019-11-16 23:39:39	1	1
58	75	4	RUB	20000.00	1	Completed	2019-11-16 23:46:19	1	1
59	76	1	RUB	10000.00	1	Completed	2019-11-16 23:51:29	1	1
61	78	13	RUB	40000.00	1	Completed	2019-11-16 23:57:29	1	1
62	79	2	RUB	100000.00	1	Completed	2019-11-17 00:47:16	1	1
65	82	5	RUB	30000.00	1	Completed	2019-11-17 00:54:42	1	1
68	83	1	RUB	10000.00	1	Completed	2019-11-17 13:15:42	1	1
69	84	1	RUB	10000.00	1	pending	2019-11-17 22:27:56	0	0
70	87	1	RUB	10000.00	1	Completed	2019-11-21 20:58:57	1	1
74	91	2	RUB	100000.00	1	Completed	2019-11-22 21:01:17	0	0
75	92	5	RUB	30000.00	1	Completed	2019-11-25 18:54:02	1	1
76	93	1	RUB	10000.00	1	pending	2019-11-26 15:19:57	0	0
77	82	1	RUB	10000.00	1	pending	2019-11-27 18:45:09	0	0
85	95	5	RUB	30000.00	1	Completed	2019-11-30 09:56:50	1	1
87	101	2	RUB	100000.00	1	Completed	2019-12-02 17:39:03	1	0
88	103	3	RUB	1000000.00	1	Completed	2019-12-20 18:14:35	1	0
89	82	2	RUB	100000.00	1	pending	2019-12-23 21:39:49	0	0
90	82	9	RUB	400000.00	1	pending	2019-12-23 21:48:30	0	0
91	104	1	RUB	10000.00	1	Completed	2020-01-06 20:26:17	1	0
92	82	2	RUB	100000.00	1	pending	2020-01-22 10:34:39	0	0
93	82	2	RUB	100000.00	1	pending	2020-01-22 10:35:11	0	0
94	105	1	RUB	10000.00	1	Completed	2020-02-09 18:43:47	1	0
95	106	1	RUB	10000.00	1	Completed	2020-02-09 20:55:53	1	0
96	107	2	RUB	100000.00	1	Completed	2020-02-09 20:57:20	1	0
97	95	2	RUB	100000.00	1	pending	2020-02-15 22:34:09	0	0

id	uid	rank_id	start_data	status
12	14	4	2019-08-01 22:04:24	1
29	32	4	2019-08-04 05:48:28	1
30	33	4	2019-08-04 05:50:57	1
31	34	4	2019-08-04 05:53:32	1
32	35	4	2019-08-09 23:50:55	1
33	36	4	2019-08-10 00:02:12	1
34	37	4	2019-08-18 23:17:16	1
39	45	6	2019-09-02 00:28:30	1
40	46	4	2019-09-02 00:32:32	1
47	55	4	2019-11-15 17:39:25	1
48	56	4	2019-11-15 18:01:09	1
49	57	4	2019-11-15 19:01:43	1
50	58	4	2019-11-15 19:33:49	1
51	59	4	2019-11-15 20:56:36	1
52	60	4	2019-08-31 22:55:33	1
53	61	4	2019-11-15 23:46:06	1
54	62	4	2019-11-17 01:10:01	1
55	63	4	2019-10-15 07:29:19	1
56	64	4	2019-10-15 07:51:57	1
57	65	4	2019-11-16 10:08:11	1
58	66	4	2019-11-16 23:00:30	1
60	68	4	2019-11-16 23:07:39	1
61	69	4	2019-11-16 23:12:32	1
62	70	4	2019-11-16 23:18:21	1
63	71	4	2019-11-16 23:26:37	1
65	73	4	2019-11-16 23:35:04	1
66	74	4	2019-11-16 23:39:38	1
67	75	4	2019-11-16 23:46:19	1
68	76	4	2019-11-16 23:51:29	1
70	78	4	2019-11-16 23:57:29	1
71	79	4	2019-11-17 00:47:16	1
74	82	4	2019-11-17 00:54:42	1
75	83	6	2019-11-17 13:15:42	1
79	87	6	2019-11-21 21:02:11	1
83	91	6	2019-11-22 21:01:17	1
85	93	6	2019-11-26 15:19:57	1
87	95	6	2019-11-28 14:34:43	1
91	101	6	2019-12-02 17:41:11	1
92	103	6	2019-12-20 18:11:23	1
93	104	6	2020-01-06 20:31:54	1
94	105	6	2020-02-09 18:43:57	1
95	107	6	2020-02-09 20:57:45	1
96	106	6	2020-02-09 20:57:53	1

id	af_id	r_w_uid	DATE
8	12	25	2019-08-04 00:57:32
11	12	28	2019-08-04 04:50:30
12	12	29	2019-08-04 04:57:18
13	12	30	2019-08-04 05:01:37
15	12	32	2019-08-04 05:48:29
16	12	33	2019-08-04 05:50:57
17	30	34	2019-08-04 05:53:32
18	12	35	2019-08-09 23:50:56
19	30	36	2019-08-10 00:02:13
20	12	37	2019-08-18 23:17:16
21	29	45	2019-09-02 00:28:30
22	29	46	2019-09-02 00:32:32
23	47	55	2019-11-15 17:44:16
24	47	56	2019-11-15 18:01:09
25	47	57	2019-11-15 19:01:44
26	49	58	2019-11-15 19:33:49
27	49	61	2019-11-15 23:46:06
28	49	62	2019-11-16 01:10:01
29	49	63	2019-11-16 07:29:19
30	55	64	2019-11-16 07:51:57
31	52	65	2019-11-16 10:08:11
32	52	66	2019-11-16 23:00:31
34	52	68	2019-11-16 23:07:39
35	52	69	2019-11-16 23:12:32
36	52	70	2019-11-16 23:18:21
37	52	71	2019-11-16 23:26:37
38	63	72	2019-11-16 23:33:07
39	63	73	2019-11-16 23:35:04
40	63	74	2019-11-16 23:39:39
41	52	75	2019-11-16 23:46:19
42	52	76	2019-11-16 23:51:29
43	67	77	2019-11-16 23:55:52
44	67	78	2019-11-16 23:57:29
45	57	79	2019-11-17 00:47:16
46	57	80	2019-11-17 00:51:22
47	57	81	2019-11-17 00:53:05
48	74	82	2019-11-17 00:54:42
49	55	83	2019-11-17 13:15:42
50	58	84	2019-11-17 22:27:56
51	55	88	2019-11-21 22:43:37
52	55	89	2019-11-22 08:27:56
53	55	90	2019-11-22 09:09:19
54	52	91	2019-11-22 21:01:17
55	83	93	2019-11-26 15:19:57
56	74	94	2019-11-28 13:57:44
57	74	95	2019-11-28 14:34:43
58	47	101	2019-12-02 17:39:03
59	49	104	2020-01-06 20:26:17
60	39	106	2020-02-09 20:55:53
61	39	107	2020-02-09 20:57:20

id	uid	SUM(amount_value * 0.1)
12	14	30000.000
29	32	10000.000
30	33	1000.000
31	34	1000.000
32	35	10000.000
33	36	10000.000
34	37	2000.000
39	45	1000.000
40	46	1000.000
47	55	10000.000
48	56	2000.000
49	57	200000.000
50	58	1000.000
52	60	100000.000
53	61	1000.000
54	62	10000.000
55	63	4000.000
56	64	3000.000
57	65	10000.000
58	66	10000.000
60	68	10000.000
61	69	100000.000
62	70	10000.000
63	71	2000.000
65	73	10000.000
66	74	40000.000
67	75	2000.000
68	76	1000.000
70	78	4000.000
71	79	10000.000
74	82	3000.000
75	83	1000.000
79	87	1000.000
83	91	10000.000
null	92	3000.000
87	95	3000.000
91	101	10000.000
92	103	100000.000
93	104	1000.000
94	105	1000.000
96	106	1000.000
95	107	10000.000