Just Data Explore SQL tests

SQL test - SQL test - SQL test - SQL test

Top Referred Topics

--[params]
-- date :start_date
-- date :end_date

SELECT
	t.id AS topic_id,
	COUNT(p.id) AS external_click_count
FROM incoming_links il
JOIN posts p ON p.id = il.post_id
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype = 'regular'
	AND il.created_at::date BETWEEN :start_date::date 
	AND :end_date::date
GROUP BY p.id, t.id
ORDER BY external_click_count DESC
LIMIT 100

Trending Search Terms

--[params]
-- date :start_date
-- date :end_date

SELECT 
	term,
	count(id) AS term_count
FROM search_logs sl
WHERE sl.created_at::date BETWEEN :start_date::date 
	AND :end_date::date
GROUP BY term
ORDER BY 1 DESC
LIMIT 100

Time to first response

--[params]
--date :start_date
--date :end_date

SELECT 
    t.id,
    t.title,
    (p.created_at-t.created_at) AS time_first_response
FROM topics t
INNER JOIN posts p ON (t.id = p.topic_id) 
WHERE 
    t.created_at::date BETWEEN :start_date::date AND :end_date::date
    AND t.deleted_at ISNULL
    AND p.deleted_at ISNULL
    AND p.post_number = 2
    AND p.reply_to_post_number = 1
    AND t.user_id > 0    
ORDER BY 3 ASC
LIMIT 100

Topics (number of Views)

--[params]
-- date :start_date
-- date :end_date

SELECT 
    id,
    views   
FROM topics t
WHERE t.created_at::date BETWEEN :start_date::date
    AND :end_date::date
    AND t.deleted_at ISNULL
ORDER BY views DESC
LIMIT 100

Topics (more :heart: Likes)

--[params]
-- date :start_date
-- date :end_date

SELECT 
    id,
    title,
    like_count   
FROM topics t
WHERE t.created_at::date BETWEEN :start_date::date
    AND :end_date::date
    AND t.deleted_at ISNULL
ORDER BY like_count DESC
LIMIT 100

Posts (more :heart: Likes)

--[params]
-- date :start_date
-- date :end_date

SELECT 
    topic_id,
    post_number,
    id,
    like_count   
FROM posts p
WHERE p.created_at::date BETWEEN :start_date::date
    AND :end_date::date
    AND p.post_number > 1
    AND p.deleted_at ISNULL
ORDER BY like_count DESC
LIMIT 100

Users (more :heart: Likes)
–[params]
– date :start_date

SELECT 
    p.user_id,
    u.username,    
    COUNT(p.post_action_type_id)
FROM post_actions p
INNER JOIN users u on (u.id = p.user_id)
WHERE 
    p.post_action_type_id = 2
    AND p.created_at::date = :start_date::date
    AND p.user_id > 0 
GROUP BY user_id, u.username
ORDER BY 3 DESC
LIMIT 100

Users (more Topics Created)
–[params]
– date :start_date

SELECT 
    user_id,
    u.username,
    COUNT(t.id)
FROM topics t
INNER JOIN users u on (u.id = t.user_id)
WHERE t.created_at::date = :start_date::date
    AND t.deleted_at ISNULL
    AND t.user_id > 0 
GROUP BY  user_id, u.username
ORDER BY 3 DESC
LIMIT 100

Users (more Replies)
–[params]
– date :start_date

SELECT 
    p.user_id,
    u.username,    
    COUNT(p.id)
FROM posts p
INNER JOIN users u on (u.id = p.user_id)
WHERE 
    p.created_at::date = :start_date::date
    AND p.deleted_at ISNULL
    AND p.post_number > 1
    AND p.user_id > 0 
GROUP BY user_id, u.username
ORDER BY 3 DESC
LIMIT 100

Dalia,

This graph shows the number of requests per page, and it is not possible to open this information by group.
Anyway, the query that originates the chart is shown below, in case you wanted to work more with this data.

image

-- [params]
-- date :start_date
-- date :end_date

SELECT
    ar.date,
    CASE 
      WHEN ar.req_type=6 THEN 'Crawlers'
      WHEN ar.req_type=7 THEN 'Logged in users'
      WHEN ar.req_type=8 THEN 'Anonymous users'
    END,
    ar.count AS views
FROM application_requests ar
WHERE req_type IN (6,7,8)
    AND ar.date::date BETWEEN :start_date::date
	AND :end_date::date
ORDER BY ar.date ASC, ar.req_type
date req_type views
2020-05-10 Crawlers 1096
2020-05-10 Logged in users 799
2020-05-10 Anonymous users 1258
2020-05-11 Crawlers 1175
2020-05-11 Logged in users 1574
2020-05-11 Anonymous users 1778

test

Before After
![](upload://vRVA1HvHCyyQa61gvWaXQJ04wcl.png) ![](upload://tAKFd5K1YPKXtf5sPHNbzSRMkHd.png)

test

Before After
![](upload://vRVA1HvHCyyQa61gvWaXQJ04wcl.png) ![](upload://tAKFd5K1YPKXtf5sPHNbzSRMkHd.png)
Before After
![](upload://vRVA1HvHCyyQa61gvWaXQJ04wcl.png) ![](upload://tAKFd5K1YPKXtf5sPHNbzSRMkHd.png)