Visibility of scheduled queries


#1

We now have over 600 queries scheduled in re:dash. I was convinced that there are probably some scheduled queries that never run or some that are never viewed so I went ahead and created this report.

SELECT '<a href="http://redash.data.com/queries/' || q.id || '/source">' || q.name || '</a>' AS query, u.name AS "owner", CASE WHEN position(':' IN q.schedule) = 0 THEN 'Every ' || justify_interval((q.schedule::text || ' second')::interval)::text ELSE q.schedule::text || ' UTC' END AS schedule, q.created_at, r.retrieved_at AS last_execution, CASE WHEN position(':' IN q.schedule) = 0 THEN r.retrieved_at + (q.schedule::text || ' second')::interval ELSE to_timestamp(to_char(CURRENT_DATE,'YYYY-MM-DD') || ' ' || q.schedule::text,'YYYY-MM-DD HH24:MI') END AS next_execution, count(e.id) as views_30_days FROM queries q JOIN users u ON (q.user_id = u.id) JOIN query_results r ON (q.latest_query_data_id = r.id) LEFT JOIN events e ON (q.id = cast(coalesce(nullif(e.object_id,''),'0') as integer) AND e.object_type = 'query' AND e.created_at > (current_date - interval '30' day) AND e.action = 'view') WHERE schedule IS NOT NULL GROUP BY 1,2,3,4,5,6 ORDER BY 1;

Please use it and let me know if you find any errors in my query or can recommend any improvements.