sessions
2 years ago
create_city.txt
2 years ago
create_country.txt
2 years ago
create_referrer.txt
3 years ago
create_resource.txt
3 years ago
create_view.txt
3 years ago
get_campaign.txt
3 years ago
get_campaigns.txt
2 years ago
get_cities.txt
2 years ago
get_city.txt
2 years ago
get_countries.txt
2 years ago
get_country.txt
2 years ago
get_current_traffic.txt
2 years ago
get_device_browsers.txt
2 years ago
get_device_operating_systems.txt
2 years ago
get_device_types.txt
2 years ago
get_referrer.txt
3 years ago
get_referrers.txt
2 years ago
get_resource.txt
3 years ago
get_resources.txt
2 years ago
get_visitors_by_minute_interval.txt
3 years ago
get_visitors_by_ten_second_interval.txt
3 years ago
get_resources.txt
50 lines
| 1 | SELECT * |
| 2 | FROM ( SELECT COUNT(DISTINCT IF(views.current_period, views.id, NULL)) AS views, |
| 3 | COUNT(DISTINCT IF(views.current_period, sessions.visitor_id, NULL)) AS visitors, |
| 4 | COUNT(DISTINCT IF(views.current_period, sessions.session_id, NULL)) AS sessions, |
| 5 | COUNT(DISTINCT IF(views.current_period AND sessions.final_view_id IS NULL, sessions.session_id, NULL)) AS bounces, |
| 6 | COUNT(DISTINCT IF(comments.current_period, comments.comment_id, NULL)) AS comments, |
| 7 | AVG( IF(views.current_period, TIMESTAMPDIFF(SECOND, views.viewed_at, views.next_viewed_at), NULL)) AS average_view_duration, |
| 8 | COUNT(DISTINCT IF(views.current_period AND resources.id = initial_view.resource_id, sessions.session_id, null)) AS entrances, |
| 9 | COUNT(DISTINCT IF(views.current_period AND (resources.id = final_view.resource_id OR (resources.id = initial_view.resource_id AND sessions.final_view_id IS NULL)), sessions.session_id, null)) AS exits, |
| 10 | COUNT(DISTINCT |
| 11 | IF(views.previous_period, views.id, NULL)) AS previous_period_views, |
| 12 | COUNT(DISTINCT |
| 13 | IF(views.previous_period, sessions.visitor_id, NULL)) AS previous_period_visitors, |
| 14 | COUNT(DISTINCT |
| 15 | IF(views.previous_period, sessions.session_id, NULL)) AS previous_period_sessions, |
| 16 | COUNT(DISTINCT |
| 17 | IF(comments.previous_period, comments.comment_id, NULL)) AS previous_period_comments, |
| 18 | AVG( IF(views.previous_period, TIMESTAMPDIFF(SECOND, views.viewed_at, views.next_viewed_at), NULL)) AS previous_period_average_view_duration, |
| 19 | resources.* |
| 20 | FROM ( SELECT views.*, |
| 21 | IF(views.viewed_at BETWEEN :start AND :end, TRUE, FALSE) AS current_period, |
| 22 | IF(views.viewed_at BETWEEN :previous_start AND :previous_end, TRUE, |
| 23 | FALSE) AS previous_period |
| 24 | FROM wp_independent_analytics_views AS views |
| 25 | WHERE views.viewed_at BETWEEN :previous_start AND :end |
| 26 | ) AS views |
| 27 | LEFT JOIN wp_independent_analytics_sessions AS sessions |
| 28 | ON views.session_id = sessions.session_id |
| 29 | LEFT JOIN wp_independent_analytics_resources AS resources |
| 30 | ON views.resource_id = resources.id |
| 31 | LEFT JOIN wp_independent_analytics_views AS initial_view ON sessions.initial_view_id = initial_view.id |
| 32 | LEFT JOIN wp_independent_analytics_views AS final_view ON sessions.final_view_id = final_view.id |
| 33 | LEFT JOIN ( SELECT comment_ID AS comment_id, |
| 34 | comment_post_ID AS post_id, |
| 35 | IF(comments.comment_date_gmt BETWEEN :start AND :end, TRUE, |
| 36 | FALSE) AS current_period, |
| 37 | IF(comments.comment_date_gmt BETWEEN :previous_start AND :previous_end, |
| 38 | TRUE, |
| 39 | FALSE) AS previous_period |
| 40 | FROM wp_comments AS comments |
| 41 | WHERE comments.comment_approved = '1' |
| 42 | AND comments.comment_date_gmt BETWEEN :previous_start AND :end |
| 43 | ) AS comments |
| 44 | ON resources.singular_id = comments.post_id |
| 45 | GROUP BY resources.id |
| 46 | ORDER BY visitors DESC, |
| 47 | views DESC |
| 48 | ) subquery |
| 49 | WHERE views > 0 OR previous_period_views > 0 |
| 50 |