Evolution of number of Confluence spaces and more per period in raw SQL

evolutions_indicators_confluence.sql

SELECT
  t1.period_greatest,
  COALESCE(SUM(t2.nb_of_spaces), 0)   as evol_nb_of_spaces,
  COALESCE(SUM(t2.nb_of_pages), 0) as evol_nb_of_pages,
  COALESCE(SUM(t2.nb_of_blogposts), 0) as evol_nb_of_blogposts,
  COALESCE(SUM(t2.nb_of_attachments), 0) as evol_nb_of_attachments,
  COALESCE(SUM(t2.nb_of_customs), 0) as evol_nb_of_customs,
  COALESCE(SUM(t2.nb_of_pages), 0)
    + COALESCE(SUM(t2.nb_of_blogposts), 0)
    + COALESCE(SUM(t2.nb_of_attachments), 0)
    + COALESCE(SUM(t2.nb_of_customs), 0) as nb_of_contents,
  COALESCE(SUM(t2.nb_of_comments), 0) as evol_nb_of_comments,
  COALESCE(SUM(t2.nb_of_likes), 0) as evol_nb_of_likes,
  COALESCE(SUM(t2.total_attachments_size), 0) as evol_total_attachments_size

FROM (
    SELECT DISTINCT
        GREATEST(r1.period, r2.period, r3.period, r4.period, r5.period) as period_greatest,
        MAX(r1.nb_of_spaces) as nb_of_spaces,
        MAX(r2.nb_of_comments) as nb_of_comments,
        MAX(r3.nb_of_likes) as nb_of_likes,
        MAX(r4.nb_of_pages) as nb_of_pages,
        MAX(r4.nb_of_blogposts) as nb_of_blogposts,
        MAX(r4.nb_of_attachments) as nb_of_attachments,
        MAX(r4.nb_of_customs) as nb_of_customs,
        MAX(r5.total_attachments_size) as total_attachments_size

    FROM (
        SELECT
            CONCAT(
                EXTRACT(YEAR FROM s.creationdate), '_',
                LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0'), '_',
                LPAD(EXTRACT(WEEK FROM s.creationdate)::TEXT, 2, '0'), '_',
                LPAD(EXTRACT(DOY FROM s.creationdate)::TEXT, 3, '0')
            ) as period,
            COUNT(s.spaceid) as nb_of_spaces
        FROM spaces s
        GROUP BY period
        ORDER BY period
    ) r1

    FULL JOIN (
        SELECT
            CONCAT(
                EXTRACT(YEAR FROM c2.creationdate), '_',
                LPAD(EXTRACT(MONTH FROM c2.creationdate)::TEXT, 2, '0'), '_',
                LPAD(EXTRACT(WEEK FROM c2.creationdate)::TEXT, 2, '0'), '_',
                LPAD(EXTRACT(DOY FROM c2.creationdate)::TEXT, 3, '0')
            ) as period,
            COUNT(c2.contenttype) as nb_of_comments
        FROM content c2
        WHERE c2.prevver IS NULL
        AND c2.content_status = 'current'
        AND c2.contenttype = 'COMMENT'
        GROUP BY period
        ORDER BY period
    ) r2 on r2.period = r1.period

    FULL JOIN (
        SELECT
            CONCAT(
                EXTRACT(YEAR FROM l.creationdate), '_',
                LPAD(EXTRACT(MONTH FROM l.creationdate)::TEXT, 2, '0'), '_',
                LPAD(EXTRACT(WEEK FROM l.creationdate)::TEXT, 2, '0'), '_',
                LPAD(EXTRACT(DOY FROM l.creationdate)::TEXT, 3, '0')
            ) as period,
            COUNT(l.id) as nb_of_likes
        FROM likes l
        JOIN content c3 ON l.contentid = c3.contentid
        WHERE c3.prevver IS NULL
          AND c3.content_status = 'current'
        GROUP BY period
        ORDER BY period
    ) r3 on r3.period = GREATEST(r1.period, r2.period)

    FULL JOIN(
        SELECT
            CONCAT(
                EXTRACT(YEAR FROM c4.creationdate), '_',
                LPAD(EXTRACT(MONTH FROM c4.creationdate)::TEXT, 2, '0'), '_',
                LPAD(EXTRACT(WEEK FROM c4.creationdate)::TEXT, 2, '0'), '_',
                LPAD(EXTRACT(DOY FROM c4.creationdate)::TEXT, 3, '0')
            ) as period,
            c4.contenttype,
            CASE WHEN c4.contenttype = 'PAGE' THEN COUNT(c4.contenttype) END       nb_of_pages,
            CASE WHEN c4.contenttype = 'BLOGPOST' THEN COUNT(c4.contenttype) END   nb_of_blogposts,
            CASE WHEN c4.contenttype = 'ATTACHMENT' THEN COUNT(c4.contenttype) END nb_of_attachments,
            CASE WHEN c4.contenttype = 'CUSTOM' THEN COUNT(c4.contenttype) END     nb_of_customs
        FROM content c4
        WHERE c4.content_status = 'current'
          AND c4.spaceid IS NOT NULL
          AND c4.prevver IS NULL
          AND c4.contenttype != 'SPACEDESCRIPTION'
        GROUP BY c4.contenttype, period
    ) r4 on r4.period = GREATEST(r1.period, r2.period, r3.period)

    FULL JOIN(
        SELECT
            CONCAT(
                    EXTRACT(YEAR FROM c5.creationdate), '_',
                    LPAD(EXTRACT(MONTH FROM c5.creationdate)::TEXT, 2, '0'), '_',
                    LPAD(EXTRACT(WEEK FROM c5.creationdate)::TEXT, 2, '0'), '_',
                    LPAD(EXTRACT(DOY FROM c5.creationdate)::TEXT, 3, '0')
            ) as period,
            SUM(cp.LONGVAL) as total_attachments_size
        FROM content c5
        JOIN content c6 ON c5.contentid = c6.pageid
        JOIN contentproperties cp ON c6.contentid = cp.contentid
        WHERE c6.contenttype = 'ATTACHMENT'
        GROUP BY period
    ) r5 on r5.period = GREATEST(r1.period, r2.period, r3.period, r4.period)

    GROUP BY r1.period, r2.period, r3.period, r4.period, r5.period
    ORDER BY period_greatest
) t1

LEFT JOIN(
    SELECT DISTINCT
        GREATEST(r1.period, r2.period, r3.period, r4.period, r5.period) as period_greatest,
        MAX(r1.nb_of_spaces) as nb_of_spaces,
        MAX(r2.nb_of_comments) as nb_of_comments,
        MAX(r3.nb_of_likes) as nb_of_likes,
        MAX(r4.nb_of_pages) as nb_of_pages,
        MAX(r4.nb_of_blogposts) as nb_of_blogposts,
        MAX(r4.nb_of_attachments) as nb_of_attachments,
        MAX(r4.nb_of_customs) as nb_of_customs,
        MAX(r5.total_attachments_size) as total_attachments_size

    FROM (
             SELECT
                 CONCAT(
                         EXTRACT(YEAR FROM s.creationdate), '_',
                         LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0'), '_',
                         LPAD(EXTRACT(WEEK FROM s.creationdate)::TEXT, 2, '0'), '_',
                         LPAD(EXTRACT(DOY FROM s.creationdate)::TEXT, 3, '0')
                     ) as period,
                 COUNT(s.spaceid) as nb_of_spaces
             FROM spaces s
             GROUP BY period
             ORDER BY period
         ) r1

             FULL JOIN (
        SELECT
            CONCAT(
                    EXTRACT(YEAR FROM c2.creationdate), '_',
                    LPAD(EXTRACT(MONTH FROM c2.creationdate)::TEXT, 2, '0'), '_',
                    LPAD(EXTRACT(WEEK FROM c2.creationdate)::TEXT, 2, '0'), '_',
                    LPAD(EXTRACT(DOY FROM c2.creationdate)::TEXT, 3, '0')
                ) as period,
            COUNT(c2.contenttype) as nb_of_comments
        FROM content c2
        WHERE c2.prevver IS NULL
          AND c2.content_status = 'current'
          AND c2.contenttype = 'COMMENT'
        GROUP BY period
        ORDER BY period
    ) r2 on r2.period = r1.period

             FULL JOIN (
        SELECT
            CONCAT(
                    EXTRACT(YEAR FROM l.creationdate), '_',
                    LPAD(EXTRACT(MONTH FROM l.creationdate)::TEXT, 2, '0'), '_',
                    LPAD(EXTRACT(WEEK FROM l.creationdate)::TEXT, 2, '0'), '_',
                    LPAD(EXTRACT(DOY FROM l.creationdate)::TEXT, 3, '0')
                ) as period,
            COUNT(l.id) as nb_of_likes
        FROM likes l
                 JOIN content c3 ON l.contentid = c3.contentid
        WHERE c3.prevver IS NULL
          AND c3.content_status = 'current'
        GROUP BY period
        ORDER BY period
    ) r3 on r3.period = GREATEST(r1.period, r2.period)

             FULL JOIN(
        SELECT
            CONCAT(
                    EXTRACT(YEAR FROM c4.creationdate), '_',
                    LPAD(EXTRACT(MONTH FROM c4.creationdate)::TEXT, 2, '0'), '_',
                    LPAD(EXTRACT(WEEK FROM c4.creationdate)::TEXT, 2, '0'), '_',
                    LPAD(EXTRACT(DOY FROM c4.creationdate)::TEXT, 3, '0')
                ) as period,
            c4.contenttype,
            CASE WHEN c4.contenttype = 'PAGE' THEN COUNT(c4.contenttype) END       nb_of_pages,
            CASE WHEN c4.contenttype = 'BLOGPOST' THEN COUNT(c4.contenttype) END   nb_of_blogposts,
            CASE WHEN c4.contenttype = 'ATTACHMENT' THEN COUNT(c4.contenttype) END nb_of_attachments,
            CASE WHEN c4.contenttype = 'CUSTOM' THEN COUNT(c4.contenttype) END     nb_of_customs
        FROM content c4
        WHERE c4.content_status = 'current'
          AND c4.spaceid IS NOT NULL
          AND c4.prevver IS NULL
          AND c4.contenttype != 'SPACEDESCRIPTION'
        GROUP BY c4.contenttype, period
    ) r4 on r4.period = GREATEST(r1.period, r2.period, r3.period)

             FULL JOIN(
        SELECT
            CONCAT(
                    EXTRACT(YEAR FROM c5.creationdate), '_',
                    LPAD(EXTRACT(MONTH FROM c5.creationdate)::TEXT, 2, '0'), '_',
                    LPAD(EXTRACT(WEEK FROM c5.creationdate)::TEXT, 2, '0'), '_',
                    LPAD(EXTRACT(DOY FROM c5.creationdate)::TEXT, 3, '0')
                ) as period,
            SUM(cp.LONGVAL) as total_attachments_size
        FROM content c5
                 JOIN content c6 ON c5.contentid = c6.pageid
                 JOIN contentproperties cp ON c6.contentid = cp.contentid
        WHERE c6.contenttype = 'ATTACHMENT'
        GROUP BY period
    ) r5 on r5.period = GREATEST(r1.period, r2.period, r3.period, r4.period)

    GROUP BY r1.period, r2.period, r3.period, r4.period, r5.period
    ORDER BY period_greatest
) t2 ON t2.period_greatest <= t1.period_greatest

GROUP BY t1.period_greatest
ORDER BY t1.period_greatest

Evolution of number of Confluence spaces and more per period in raw SQL

How to get the number of Confluence spaces by month with one query SQL ?

Ok let's see how to do it step by step.

Context

The goal is to build an SQL query to count the number of spaces in an Atlassian server or Atlassian Datacenter instance (*). To do this, we will make a count cummulative total in SQL. We want to obtain this dataset, for example to draw an evolution graph.

period	nb_of_spaces	evol_nb_of_spaces
2019	10	10
2020	20	30
2021	20	50

(*) Tested only on Confluence server 7.13 with PostgreSQL. The query is written for PostgreSQL database.

Building this query step by step

Definition of the data granularity by year

SELECT
  EXTRACT(YEAR FROM s.creationdate) as period
FROM spaces s

period
2020
2021

Definition of the data granularity by month

SELECT
  CONCAT(
    EXTRACT(YEAR FROM s.creationdate), '_', 
    LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0')
  ) as period
FROM spaces s
GROUP BY period
ORDER BY period

period
2020_02
2020_03

Definition of the data granularity by week

SELECT
    CONCAT(
        EXTRACT(YEAR FROM s.creationdate), '_',
        LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0'), '_',
        LPAD(EXTRACT(WEEK FROM s.creationdate)::TEXT, 2, '0')
    ) as period
FROM spaces s
GROUP BY period
ORDER BY period

period
2020_09_36
2020_09_37

Definition of the data granularity by doy (day of the year)

SELECT
    CONCAT(
        EXTRACT(YEAR FROM s.creationdate), '_',
        LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0'), '_',
        LPAD(EXTRACT(WEEK FROM s.creationdate)::TEXT, 2, '0'), '_',
        LPAD(EXTRACT(DOY FROM s.creationdate)::TEXT, 3, '0')
    ) as period
FROM spaces s
GROUP BY period
ORDER BY period

period
2020_09_36_240
2020_09_37_241

Count the number of Confluence spaces by period

SELECT
    CONCAT(
        EXTRACT(YEAR FROM s.creationdate), '_',
        LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0')
    ) as period,
    COUNT(s.spaceid) as nb_of_spaces

FROM spaces s
GROUP BY period
ORDER BY period

period	nb_of_spaces
2020_09	10
2020_10	20
2020_11	20

Count the cumulative total of number of spaces by period

Two solutions to do this :

• with the clause OVER, that defines a windows functrion SQL.
• or with only SQL subqueries. This last solution is the fastest but it is very verbose and not pleasant to read. The performances are increased especially if we add the calculation of other evolutions of indicators, such as for example the monitoring of the number of pages, the number of attached files or the consumption of disk space.

Solution with partition (clause SQL OVER)

SELECT
  CONCAT(
    EXTRACT(YEAR FROM s.creationdate), '_',
    LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0')
  ) as period,

  SUM(COUNT(s.spaceid)) OVER (
    ORDER BY CONCAT(EXTRACT(YEAR FROM s.creationdate), '_', LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0'))
  ) as nb_of_spaces

FROM spaces s
GROUP BY period
ORDER BY period

Solution with SQL subqueries

SELECT
  t1.period,
  MAX(t1.nb_of_spaces) as nb_of_spaces,
  COALESCE(SUM(t2.nb_of_spaces), 0) as evol_nb_of_spaces 

FROM (
  SELECT DISTINCT
    r1.period,
    MAX(r1.nb_of_spaces) as nb_of_spaces
  FROM (
    SELECT
      CONCAT(
        EXTRACT(YEAR FROM s.creationdate), '_', 
        LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0')
      ) as period,
      COUNT(s.spaceid) as nb_of_spaces
    FROM spaces s
    GROUP BY period
  ) r1
  GROUP BY r1.period
) t1

LEFT JOIN(
  SELECT DISTINCT
    r1.period,
    MAX(r1.nb_of_spaces) as nb_of_spaces
  FROM (
    SELECT
      CONCAT(
        EXTRACT(YEAR FROM s.creationdate), '_', 
        LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0')
      ) as period,
      COUNT(s.spaceid) as nb_of_spaces
    FROM spaces s
    GROUP BY period
  ) r1
  GROUP BY r1.period
) t2 ON t2.period <= t1.period

GROUP BY t1.period
ORDER BY t1.period

period	nb_of_spaces	evol_nb_of_spaces
2020_09	10	10
2020_10	20	30
2020_11	20	50

Here we go !

Bonus (trick)

Wanna more indicators tracking ? 

In this request you can get the evolution of the number of spaces per period, depending on the chosen data granularity : year, month, week or doy. But it may be interesting to retrieve more indicators, like :

• evolution of number of pages,
• evolution of number of blogposts,
• evolution of number of attachments,
• evolution of number of customs,
• evolution of number of contents,
• evolution of number of comments,
• evolution of number of likes,
• evolution of total attachment size

To do this, we only need to make a full outer join of each dataset.