SELECT DATE_FORMAT(view_time, "%Y-%m-%d %H:00:00") as "Date", COUNT(distinct element_id) as "Elements Viewed"
FROM dashboard_element_view_log_detail 
WHERE view_time > :measurement_time [:last-measurement_time for Metrics]
GROUP BY 1

SELECT DATE(view_time) "Date", COUNT(distinct element_id) "Elements Viewed"
FROM dashboard_element_view_log_detail dash
WHERE  view_time > :measurement_time
GROUP BY 1

SELECT devld.element_id AS "Element ID", 
de.name AS "Element Name", 
count(distinct devld.user_id) AS "Number of Distinct Portal Users",
count(devld.user_id) AS "Total Number of Views"
FROM dashboard_element_view_log_detail AS devld    
JOIN dashboard_element de ON (devld.element_id = de.element_id)
WHERE DATE(devld.view_time)>(curdate()- interval 1 month)
GROUP BY 1
ORDER BY 4 desc Limit 10

SELECT user_id, ifnull(count(DISTINCT element_id)/DATEDIFF(MAX(view_time),MIN(view_time)), 'N/A') as AVG_number_of_tiles_per_period
FROM dashboard_element_view_log_detail
GROUP BY 1;

SELECT u.user_id AS User_id, u.username AS User_name, de.name AS Viewed_element_name, dc.category AS Element_category_name, 
IFNULL(devl.last_view_time, 'Not Available') AS Last_viewed_on  
FROM dashboard_element_view_log AS devl
JOIN user AS u ON(u.user_id=devl.user_id)
JOIN dashboard_element AS de ON(de.element_id=devl.element_id)
JOIN dashboard_category AS dc ON(dc.category_id=de.category_id)
ORDER BY last_view_time DESC

SELECT usr.username as Username, elmt.name,cat.category,dev.view_time AS Accessed_time
FROM dashboard_element_view_log_detail AS dev
JOIN user AS usr ON dev.user_id = usr.user_id
join dashboard_element as elmt on dev.element_id =elmt.element_id
JOIN (
SELECT category_id, category
FROM dashboard_category
WHERE category_id=137
UNION 
SELECT ca.category_id,dc.category
FROM category_ancestors AS ca
JOIN dashboard_category AS dc ON (dc.category_id=ca.category_id)
WHERE ancestor_category_id=137
) AS cat on elmt.category_id=cat.category_id
ORDER BY dev.view_time desc

SELECT de.element_id AS Element_id, de.name AS Element_name, devld.view_time AS Viewed_time, u.username AS User
, SUBSTRING(uco.overlay_state,10,POSITION('"' IN REPLACE(uco.overlay_state,'{"view":"',''))-1) AS View_name
 FROM dashboard_element AS de 
 JOIN dashboard_element_view_log_detail AS devld ON(devld.element_id=de.element_id)
 JOIN user_chart_overlay AS uco ON(de.element_id=uco.element_id)
 JOIN user AS u ON(u.user_id=devld.user_id)
 WHERE uco.overlay_state LIKE '{"view":"%'
 GROUP BY u.user_id

SELECT u.username AS User, u.user_id AS User_ID, de.name AS Metric_Name, de.element_id AS Metric_ID, sv.value_display_name AS Seen_Dimension_Values, uco.last_updated_time AS View_time, REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(uco.overlay_state,'"interval_unit":"',-1),'}',1),'","interval_value":',' ') AS Last_Viewed_Time_interval
FROM user_chart_overlay AS uco
JOIN user AS u ON(u.user_id=uco.user_id)
JOIN dashboard_element AS de ON(uco.element_id=de.element_id)
JOIN segment_value AS sv ON(uco.segment_value_id=sv.segment_value_id)
ORDER BY u.username, uco.last_updated_time DESC, sv.value_display_name

SELECT de.dataset_id As Dataset_ID, d.name AS Dataset_Name, count(distinct de.element_id) AS Number_of_element
FROM dashboard_element AS de
JOIN dataset AS d ON(de.dataset_id=d.dataset_id)
GROUP BY de.dataset_id
ORDER BY 3 Desc LIMIT 10