0 Replies Latest reply on Apr 28, 2010 6:33 AM by jsb2564

    Most Viewed Content

    jsb2564

      JIVE,

       

      Hi, I'm new to the Jivespace community. Trying to create a query that allows me to segment blogs and other content by most viewed. Any suggestions? Using the query below but I can't get the data I'm need.

       

       

      SELECT w.activity_ts, w.user_id, w.firstname, w.lastname, w.username, w.email, w.activity_type, w.space_name, up.value as region  
      FROM (    SELECT DISTINCT u.user_id, u.firstname, u.lastname, u.username, u.email, f.activity_type, c.name as space_name, f.activity_ts
          FROM jivedw_user u, jivedw_activity_fact f, jivedw_container c
          WHERE f.direct_object_type = 37
          AND f.activity_type = 20
          /* View: 10, Create: 20, Delete: 30, Modify: 40 */
          AND u.user_id = f.user_id
          AND f.dw_container_id = c.dw_container_id
          ORDER BY f.activity_ts) w
      LEFT JOIN ( SELECT * FROM jivedw_userprofile WHERE field_id = 5011 ) up
      ON w.user_id = up.user_id

       

      Thanks,

      John