· 6 years ago · Jun 30, 2019, 02:02 PM
1$sql = sprintf('
2 SELECT video.id, video.name, video.created_at, video.duration,
3 COUNT(DISTINCT loads.id) AS loads,
4 COUNT(DISTINCT plays.id) AS plays,
5 COUNT(DISTINCT pauses.id) AS pauses,
6 COUNT(DISTINCT completions.id) AS completions,
7 COUNT(DISTINCT seeks.id) AS seeks,
8 ROUND( IFNULL( AVG(seeks.percentage), 0 ), 2 ) AS average_seek_percentage,
9 ROUND( IFNULL( AVG(pauses.seconds), 0 ), 2 ) AS average_pause_location,
10 ( ROUND( IFNULL( AVG(load_progress.percentage), 0 ), 2 ) * 100 ) AS average_load_progress,
11 ROUND(IFNULL( AVG(play_progress.seconds), 0 ), 2 ) AS average_play_progress
12 FROM ' . $stats_videos . ' video
13 LEFT JOIN (
14 SELECT *
15 FROM ' . $stats_loads . ' loads
16 WHERE loads.video_id = %3$d
17 AND loads.created_at >= "%1$s"
18 AND loads.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
19 ) loads
20 ON loads.video_id = video.id
21 LEFT JOIN (
22 SELECT *
23 FROM ' . $stats_plays . ' plays
24 WHERE plays.video_id = %3$d
25 AND plays.created_at >= "%1$s"
26 AND plays.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
27 ) plays
28 ON plays.video_id = video.id
29 LEFT JOIN (
30 SELECT *
31 FROM ' . $stats_pauses . ' pauses
32 WHERE pauses.video_id = %3$d
33 AND pauses.created_at >= "%1$s"
34 AND pauses.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
35 ) pauses
36 ON pauses.video_id = video.id
37 LEFT JOIN (
38 SELECT *
39 FROM ' . $stats_completions . ' completions
40 WHERE completions.video_id = %3$d
41 AND completions.created_at >= "%1$s"
42 AND completions.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
43 ) completions
44 ON completions.video_id = video.id
45 LEFT JOIN (
46 SELECT *
47 FROM ' . $stats_seeks . ' seeks
48 WHERE seeks.video_id = %3$d
49 AND seeks.created_at >= "%1$s"
50 AND seeks.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
51 ) seeks
52 ON seeks.video_id = video.id
53 LEFT JOIN (
54 SELECT *
55 FROM ' . $stats_load_progress . ' load_progress
56 WHERE load_progress.video_id = %3$d
57 AND load_progress.created_at >= "%1$s"
58 AND load_progress.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
59 ) load_progress
60 ON load_progress.video_id = video.id
61 LEFT JOIN (
62 SELECT *
63 FROM ' . $stats_play_progress . ' play_progress
64 WHERE play_progress.video_id = %3$d
65 AND play_progress.created_at >= "%1$s"
66 AND play_progress.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
67 ) play_progress
68 ON play_progress.video_id = video.id
69 WHERE video.id = %3$d;',
70 $start_date,
71 $end_date,
72 $this->_video_id
73);
74
75--
76-- Table structure for table `calendar`
77--
78
79DROP TABLE IF EXISTS `calendar`;
80/*!40101 SET @saved_cs_client = @@character_set_client */;
81/*!40101 SET character_set_client = utf8 */;
82CREATE TABLE `calendar` (
83 `adate` date NOT NULL,
84 PRIMARY KEY (`adate`)
85) ENGINE=InnoDB DEFAULT CHARSET=latin1;
86/*!40101 SET character_set_client = @saved_cs_client */;
87
88--
89-- Table structure for table `completions`
90--
91
92DROP TABLE IF EXISTS `completions`;
93/*!40101 SET @saved_cs_client = @@character_set_client */;
94/*!40101 SET character_set_client = utf8 */;
95CREATE TABLE `completions` (
96 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
97 `session_id` int(11) unsigned NOT NULL,
98 `video_id` int(11) unsigned NOT NULL,
99 `page_id` int(11) unsigned NOT NULL,
100 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
101 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
102 PRIMARY KEY (`id`),
103 KEY `completions_ibfk_1` (`session_id`),
104 KEY `completions_ibfk_2` (`video_id`),
105 KEY `completions_ibfk_3` (`page_id`),
106 CONSTRAINT `completions_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
107 CONSTRAINT `completions_ibfk_2` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
108 CONSTRAINT `completions_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
109) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
110/*!40101 SET character_set_client = @saved_cs_client */;
111
112
113--
114-- Table structure for table `load_progress`
115--
116
117DROP TABLE IF EXISTS `load_progress`;
118/*!40101 SET @saved_cs_client = @@character_set_client */;
119/*!40101 SET character_set_client = utf8 */;
120CREATE TABLE `load_progress` (
121 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
122 `session_id` int(11) unsigned NOT NULL,
123 `video_id` int(11) unsigned NOT NULL,
124 `page_id` int(11) unsigned NOT NULL,
125 `seconds` float(8,3) unsigned NOT NULL,
126 `duration` float(8,3) unsigned NOT NULL,
127 `percentage` float(8,3) unsigned NOT NULL,
128 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
129 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
130 PRIMARY KEY (`id`),
131 UNIQUE KEY `vimeography_stats_load_progress_unique` (`session_id`,`id`,`video_id`,`page_id`),
132 KEY `load_progress_ibfk_2` (`video_id`),
133 KEY `load_progress_ibfk_3` (`page_id`),
134 CONSTRAINT `load_progress_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
135 CONSTRAINT `load_progress_ibfk_2` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
136 CONSTRAINT `load_progress_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
137) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8;
138/*!40101 SET character_set_client = @saved_cs_client */;
139
140
141--
142-- Table structure for table `loads`
143--
144
145DROP TABLE IF EXISTS `loads`;
146/*!40101 SET @saved_cs_client = @@character_set_client */;
147/*!40101 SET character_set_client = utf8 */;
148CREATE TABLE `loads` (
149 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
150 `session_id` int(11) unsigned NOT NULL,
151 `video_id` int(11) unsigned NOT NULL,
152 `page_id` int(11) unsigned NOT NULL,
153 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
154 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
155 PRIMARY KEY (`id`),
156 KEY `loads_ibfk_1` (`session_id`),
157 KEY `loads_ibfk_2` (`video_id`),
158 KEY `loads_ibfk_3` (`page_id`),
159 CONSTRAINT `loads_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
160 CONSTRAINT `loads_ibfk_2` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
161 CONSTRAINT `loads_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
162) ENGINE=InnoDB AUTO_INCREMENT=800 DEFAULT CHARSET=utf8;
163/*!40101 SET character_set_client = @saved_cs_client */;
164
165--
166-- Table structure for table `pages`
167--
168
169DROP TABLE IF EXISTS `pages`;
170/*!40101 SET @saved_cs_client = @@character_set_client */;
171/*!40101 SET character_set_client = utf8 */;
172CREATE TABLE `pages` (
173 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
174 `url` text NOT NULL,
175 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
176 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
177 PRIMARY KEY (`id`)
178) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
179/*!40101 SET character_set_client = @saved_cs_client */;
180
181
182--
183-- Table structure for table `pauses`
184--
185
186DROP TABLE IF EXISTS `pauses`;
187/*!40101 SET @saved_cs_client = @@character_set_client */;
188/*!40101 SET character_set_client = utf8 */;
189CREATE TABLE `pauses` (
190 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
191 `session_id` int(11) unsigned NOT NULL,
192 `video_id` int(11) unsigned NOT NULL,
193 `page_id` int(11) unsigned NOT NULL,
194 `seconds` float(8,3) unsigned NOT NULL,
195 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
196 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
197 PRIMARY KEY (`id`),
198 KEY `pauses_ibfk_1` (`session_id`),
199 KEY `pauses_ibfk_2` (`video_id`),
200 KEY `pauses_ibfk_3` (`page_id`),
201 CONSTRAINT `pauses_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
202 CONSTRAINT `pauses_ibfk_2` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
203 CONSTRAINT `pauses_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
204) ENGINE=InnoDB AUTO_INCREMENT=161 DEFAULT CHARSET=utf8;
205/*!40101 SET character_set_client = @saved_cs_client */;
206
207--
208-- Table structure for table `play_progress`
209--
210
211DROP TABLE IF EXISTS `play_progress`;
212/*!40101 SET @saved_cs_client = @@character_set_client */;
213/*!40101 SET character_set_client = utf8 */;
214CREATE TABLE `play_progress` (
215 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
216 `session_id` int(11) unsigned NOT NULL,
217 `video_id` int(11) unsigned NOT NULL,
218 `page_id` int(11) unsigned NOT NULL,
219 `seconds` float(8,3) unsigned NOT NULL,
220 `duration` float(8,3) unsigned NOT NULL,
221 `percentage` float(8,3) unsigned NOT NULL,
222 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
223 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
224 PRIMARY KEY (`id`),
225 UNIQUE KEY `vimeography_stats_play_progress_unique` (`session_id`,`id`,`video_id`,`page_id`),
226 KEY `play_progress_ibfk_2` (`video_id`),
227 KEY `play_progress_ibfk_3` (`page_id`),
228 CONSTRAINT `play_progress_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
229 CONSTRAINT `play_progress_ibfk_2` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
230 CONSTRAINT `play_progress_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
231) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=utf8;
232/*!40101 SET character_set_client = @saved_cs_client */;
233
234--
235-- Table structure for table `plays`
236--
237
238DROP TABLE IF EXISTS `plays`;
239/*!40101 SET @saved_cs_client = @@character_set_client */;
240/*!40101 SET character_set_client = utf8 */;
241CREATE TABLE `plays` (
242 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
243 `session_id` int(11) unsigned NOT NULL,
244 `video_id` int(11) unsigned NOT NULL,
245 `page_id` int(11) unsigned NOT NULL,
246 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
247 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
248 PRIMARY KEY (`id`),
249 KEY `plays_ibfk_1` (`session_id`),
250 KEY `plays_ibfk_2` (`video_id`),
251 KEY `plays_ibfk_3` (`page_id`),
252 CONSTRAINT `plays_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
253 CONSTRAINT `plays_ibfk_2` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
254 CONSTRAINT `plays_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
255) ENGINE=InnoDB AUTO_INCREMENT=223 DEFAULT CHARSET=utf8;
256/*!40101 SET character_set_client = @saved_cs_client */;
257
258--
259-- Table structure for table `seeks`
260--
261
262DROP TABLE IF EXISTS `seeks`;
263/*!40101 SET @saved_cs_client = @@character_set_client */;
264/*!40101 SET character_set_client = utf8 */;
265CREATE TABLE `seeks` (
266 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
267 `session_id` int(11) unsigned NOT NULL,
268 `video_id` int(11) unsigned NOT NULL,
269 `page_id` int(11) unsigned NOT NULL,
270 `seconds` float(8,3) unsigned NOT NULL,
271 `duration` float(8,3) unsigned NOT NULL,
272 `percentage` float(8,3) unsigned NOT NULL,
273 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
274 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
275 PRIMARY KEY (`id`),
276 UNIQUE KEY `vimeography_stats_seeks_unique` (`session_id`,`id`,`video_id`,`page_id`),
277 KEY `seeks_ibfk_2` (`video_id`),
278 KEY `seeks_ibfk_3` (`page_id`),
279 CONSTRAINT `seeks_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
280 CONSTRAINT `seeks_ibfk_2` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
281 CONSTRAINT `seeks_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
282) ENGINE=InnoDB AUTO_INCREMENT=480 DEFAULT CHARSET=utf8;
283/*!40101 SET character_set_client = @saved_cs_client */;
284
285--
286-- Table structure for table `sessions`
287--
288
289DROP TABLE IF EXISTS `sessions`;
290/*!40101 SET @saved_cs_client = @@character_set_client */;
291/*!40101 SET character_set_client = utf8 */;
292CREATE TABLE `sessions` (
293 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
294 `token` varchar(32) NOT NULL,
295 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
296 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
297 PRIMARY KEY (`id`),
298 UNIQUE KEY `token` (`token`)
299) ENGINE=InnoDB AUTO_INCREMENT=816 DEFAULT CHARSET=utf8;
300/*!40101 SET character_set_client = @saved_cs_client */;
301
302--
303-- Table structure for table `videos`
304--
305
306DROP TABLE IF EXISTS `videos`;
307/*!40101 SET @saved_cs_client = @@character_set_client */;
308/*!40101 SET character_set_client = utf8 */;
309CREATE TABLE `videos` (
310 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
311 `name` varchar(256) NOT NULL,
312 `uri` varchar(150) NOT NULL,
313 `duration` float(8,3) unsigned NOT NULL,
314 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
315 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
316 PRIMARY KEY (`id`),
317 UNIQUE KEY `uri` (`uri`)
318) ENGINE=InnoDB AUTO_INCREMENT=402 DEFAULT CHARSET=utf8;
319/*!40101 SET character_set_client = @saved_cs_client */;
320/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
321
322/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
323/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
324/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
325/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
326/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
327/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
328/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
329
330*************************** 1. row ***************************
331 id: 1
332 select_type: SIMPLE
333 table: video
334 type: const
335possible_keys: PRIMARY
336 key: PRIMARY
337 key_len: 4
338 ref: const
339 rows: 1
340 Extra:
341*************************** 2. row ***************************
342 id: 1
343 select_type: SIMPLE
344 table: loads
345 type: ref
346possible_keys: loads_ibfk_2
347 key: loads_ibfk_2
348 key_len: 4
349 ref: const
350 rows: 6
351 Extra: Using where
352*************************** 3. row ***************************
353 id: 1
354 select_type: SIMPLE
355 table: plays
356 type: ref
357possible_keys: plays_ibfk_2
358 key: plays_ibfk_2
359 key_len: 4
360 ref: const
361 rows: 3
362 Extra: Using where
363*************************** 4. row ***************************
364 id: 1
365 select_type: SIMPLE
366 table: pauses
367 type: ref
368possible_keys: pauses_ibfk_2
369 key: pauses_ibfk_2
370 key_len: 4
371 ref: const
372 rows: 2
373 Extra: Using where
374*************************** 5. row ***************************
375 id: 1
376 select_type: SIMPLE
377 table: completions
378 type: ref
379possible_keys: completions_ibfk_2
380 key: completions_ibfk_2
381 key_len: 4
382 ref: const
383 rows: 1
384 Extra: Using where
385*************************** 6. row ***************************
386 id: 1
387 select_type: SIMPLE
388 table: seeks
389 type: ref
390possible_keys: seeks_ibfk_2
391 key: seeks_ibfk_2
392 key_len: 4
393 ref: const
394 rows: 14
395 Extra: Using where
396*************************** 7. row ***************************
397 id: 1
398 select_type: SIMPLE
399 table: load_progress
400 type: ref
401possible_keys: load_progress_ibfk_2
402 key: load_progress_ibfk_2
403 key_len: 4
404 ref: const
405 rows: 1
406 Extra: Using where
407*************************** 8. row ***************************
408 id: 1
409 select_type: SIMPLE
410 table: play_progress
411 type: ref
412possible_keys: play_progress_ibfk_2
413 key: play_progress_ibfk_2
414 key_len: 4
415 ref: const
416 rows: 1
417 Extra: Using where
418
419MariaDB [production]> SELECT video.id, video.name, video.created_at, video.duration,
420 -> COUNT(DISTINCT loads.id) AS loads,
421 -> COUNT(DISTINCT plays.id) AS plays,
422 -> COUNT(DISTINCT pauses.id) AS pauses,
423 -> COUNT(DISTINCT completions.id) AS completions,
424 -> COUNT(DISTINCT seeks.id) AS seeks
425 -> FROM videos video
426 -> LEFT JOIN (
427 -> SELECT *
428 -> FROM loads loads
429 -> WHERE loads.video_id = 5
430 -> AND loads.created_at >= "2015-10-07 19:29:27"
431 -> AND loads.created_at < DATE_ADD("2015-10-14 19:29:27", INTERVAL 1 DAY)
432 -> ) loads
433 -> ON loads.video_id = video.id
434 -> LEFT JOIN (
435 -> SELECT *
436 -> FROM plays plays
437 -> WHERE plays.video_id = 5
438 -> AND plays.created_at >= "2015-10-07 19:29:27"
439 -> AND plays.created_at < DATE_ADD("2015-10-14 19:29:27", INTERVAL 1 DAY)
440 -> ) plays
441 -> ON plays.video_id = video.id
442 -> LEFT JOIN (
443 -> SELECT *
444 -> FROM pauses pauses
445 -> WHERE pauses.video_id = 5
446 -> AND pauses.created_at >= "2015-10-07 19:29:27"
447 -> AND pauses.created_at < DATE_ADD("2015-10-14 19:29:27", INTERVAL 1 DAY)
448 -> ) pauses
449 -> ON pauses.video_id = video.id
450 -> LEFT JOIN (
451 -> SELECT *
452 -> FROM completions completions
453 -> WHERE completions.video_id = 5
454 -> AND completions.created_at >= "2015-10-07 19:29:27"
455 -> AND completions.created_at < DATE_ADD("2015-10-14 19:29:27", INTERVAL 1 DAY)
456 -> ) completions
457 -> ON completions.video_id = video.id
458 -> LEFT JOIN (
459 -> SELECT *
460 -> FROM seeks seeks
461 -> WHERE seeks.video_id = 5
462 -> AND seeks.created_at >= "2015-10-07 19:29:27"
463 -> AND seeks.created_at < DATE_ADD("2015-10-14 19:29:27", INTERVAL 1 DAY)
464 -> ) seeks
465 -> ON seeks.video_id = video.id
466 -> WHERE video.id = 5;
467+----+----------------------+---------------------+----------+-------+-------+--------+-------------+-------+
468| id | name | created_at | duration | loads | plays | pauses | completions | seeks |
469+----+----------------------+---------------------+----------+-------+-------+--------+-------------+-------+
470| 5 | Intro to Motorcycles | 2015-10-06 20:37:21 | 348.000 | 44 | 25 | 16 | 4 | 65 |
471+----+----------------------+---------------------+----------+-------+-------+--------+-------------+-------+
4721 row in set (10.75 sec)
473
474Group by video.id, video.name, video.created_at, video.duration
475
476LEFT JOIN (
477 SELECT *
478 FROM ' . $stats_loads . ' loads
479 WHERE loads.video_id = %3$d
480 AND loads.created_at >= "%1$s"
481 AND loads.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
482 ) loads
483
484 LEFT JOIN (
485 SELECT id
486 FROM ' . $stats_loads . ' loads
487 WHERE loads.video_id = %3$d
488 AND loads.created_at >= "%1$s"
489 AND loads.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
490 ) loads