CoCalc Logo Icon
StoreFeaturesDocsShareSupportNewsAboutSign UpSign In
sagemathinc

Real-time collaboration for Jupyter Notebooks, Linux Terminals, LaTeX, VS Code, R IDE, and more,
all in one place.

GitHub Repository: sagemathinc/cocalc
 Path: blob/master/src/packages/database/postgres/retention/active-users.ts
Views: 687
1
import getLogger from "@cocalc/backend/logger";

2
const log = getLogger("database:retention:active-users");

3


4
export default async function activeUsers({

5
  model,

6
  table,

7
  last_start_time,

8
  pool,

9
  start,

10
  stop,

11
  period,

12
}) {

13
  const query = `WITH

14
periods0 AS (

15
  SELECT $1::timestamp + (n * $2::interval) AS period_start,

16
         $1::timestamp + ((n + 1) * $2::interval) AS period_end

17
  FROM generate_series(0, floor(EXTRACT(EPOCH FROM (now() - $1::timestamp - '1 second'::interval)) / EXTRACT(EPOCH FROM $2::interval))::integer) AS n

18
  ),

19
periods AS (SELECT * FROM periods0 ${

20
    last_start_time == null ? "" : "WHERE period_start > $3"

21
  }),

22
period_counts AS (

23
  SELECT periods.period_start, COUNT(DISTINCT ${table}.account_id) AS count

24
  FROM periods

25
  LEFT JOIN ${table} ON ${table}.time >= periods.period_start AND ${table}.time < periods.period_end

26
  GROUP BY periods.period_start

27
)

28
SELECT periods.period_start, periods.period_end, COALESCE(period_counts.count, 0) AS count

29
FROM periods

30
LEFT JOIN period_counts ON periods.period_start = period_counts.period_start

31
WHERE periods.period_end <= NOW()

32
ORDER BY periods.period_start`;

33


34
  const getSize = async (rows) => {

35
    if (rows.length == 0) return 0;

36
    return (

37
      await pool.query(

38
        `SELECT COUNT(DISTINCT(account_id)) as size FROM ${table} WHERE time >= $1::timestamp AND time < $2::timestamp`,

39
        [start, rows[rows.length - 1].period_end]

40
      )

41
    ).rows[0].size;

42
  };

43


44
  if (last_start_time == null) {

45
    log.debug("just compute all the data");

46
    const { rows } = await pool.query(query, [start, period]);

47
    if (rows.length == 0) {

48
      // shouldn't happen because should get excluded above...

49
      return;

50
    }

51
    const active = rows.map((x) => parseInt(x.count));

52
    const last_start_time = rows[rows.length - 1].period_start;

53
    await pool.query(

54
      "INSERT INTO crm_retention(start,stop,model,period,active,last_start_time,size) VALUES($1,$2,$3,$4,$5,$6,$7)",

55
      [start, stop, model, period, active, last_start_time, await getSize(rows)]

56
    );

57
  } else {

58
    log.debug("compute the missing data and put it into the database");

59
    const { rows } = await pool.query(query, [start, period, last_start_time]);

60
    if (rows.length == 0) {

61
      // shouldn't happen because should get excluded...

62
      return;

63
    }

64
    const active = rows.map((x) => parseInt(x.count));

65
    const new_last_start_time = rows[rows.length - 1].period_start;

66
    await pool.query(

67
      "UPDATE crm_retention SET last_start_time=$5::timestamp, active = array_cat(active, $6::integer[]) WHERE start=$1 AND stop=$2 AND model=$3 AND period=$4 AND size=$7",

68
      [

69
        start,

70
        stop,

71
        model,

72
        period,

73
        new_last_start_time,

74
        active,

75
        await getSize(rows),

76
      ]

77
    );

78
  }

79
}

80


81
/*

82
WITH

83
periods AS (

84
  SELECT '2023-04-03'::timestamp + (n * '1 day'::interval) AS period_start,

85
         '2023-04-03'::timestamp + ((n + 1) * '1 day'::interval) AS period_end

86
  FROM generate_series(0, floor(EXTRACT(EPOCH FROM (now() - '2023-04-03'::timestamp - '1 second'::interval)) / EXTRACT(EPOCH FROM '1 day'::interval))::integer) AS n

87
  ),

88
period_counts AS (

89
  SELECT periods.period_start, COUNT(DISTINCT file_access_log.account_id) AS count

90
  FROM periods

91
  LEFT JOIN file_access_log ON file_access_log.time >= periods.period_start AND file_access_log.time < periods.period_end

92
  GROUP BY periods.period_start

93
)

94
SELECT periods.period_start, periods.period_end, COALESCE(period_counts.count, 0) AS count

95
FROM periods

96
LEFT JOIN period_counts ON periods.period_start = period_counts.period_start

97
WHERE periods.period_end <= NOW()

98
ORDER BY periods.period_start;

99


100
*/

101


102