kekpek.py

#!/usb/bin/env python

import psycopg2
import argparse

def get_xaily_messages(conn, x):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT COUNT(DISTINCT message_id), date_trunc('%s', date) AS quant
            FROM history_messages
            GROUP BY 2
            ORDER BY 2 DESC
        """ % x)
        for res in cur:
            yield res

def get_groups_total(conn):
    with conn.cursor() as cur:
        cur.execute("SELECT COUNT(id) FROM groups")
        return cur.fetchone()[0]

def get_avg_msgs_per_user_per_xay(conn, x):
    with conn.cursor() as cur:
        cur.execute("""
            WITH messages AS (
                SELECT COUNT(h.message_id) AS avr, h.creator_user_id AS author, date_trunc('%s', date) AS quant
                FROM history_messages h
                WHERE h.message_id IN (SELECT DISTINCT message_id FROM history_messages)
                  AND h.message_content_header <> 2
                  AND h.creator_user_id NOT IN (0, 10)
                GROUP BY 3, 2
                ORDER BY 3, 2
            )
            SELECT AVG(ms.avr), ms.quant FROM messages AS ms
            GROUP BY 2
            ORDER BY 2 DESC
        """ % x)
        for r in cur:
            yield r

def get_avg_groups_per_member_xaily(conn, x):
    with conn.cursor() as cur:
        cur.execute("""
            WITH xaily_user_groups AS (
                SELECT gu.user_id, COUNT(gu.group_id) AS cnt, date_trunc('%s', gu.joined_at) AS quant
                FROM group_users AS gu
                GROUP BY gu.user_id, 3
            )
            SELECT AVG(x.cnt), x.quant FROM xaily_user_groups AS x
            GROUP BY 2
            ORDER BY 2 DESC

        """ % x)
        for r in cur:
            yield r

def get_xau(conn, x):
    with conn.cursor() as cur:
        cur.execute("""
        SELECT COUNT(DISTINCT user_id), date_trunc('%s', changed) AS quant
        FROM conversation_message_statuses
        WHERE action = 'read'
        GROUP BY 2
        ORDER BY 2 DESC
        """ % x)
        for r in cur:
            yield r

def get_groups_by_xau(conn, x):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT COUNT(id), date_trunc('%s', created_at) AS quant
            FROM groups
            GROUP BY 2
            ORDER BY 2 DESC
        """ % x)
        for r in cur:
            yield r

if __name__ == '__main__':
    parser = argparse.ArgumentParser()
    parser.add_argument('host')
    parser.add_argument('user')
    parser.add_argument('password')
    parser.add_argument('dbname')
    parser.add_argument('-p','--port', dest='port', type=int, default=5432, action='store')
    args = parser.parse_args()

    conn = psycopg2.connect(dbname=args.dbname, user=args.user, password=args.password, host=args.host, port=args.port)

    with open('stats_%s.csv' % args.host, 'w') as csv:
        csv.write('Daily messages\n')
        csv.write('Date, Messages\n')
        for r, d in get_xaily_messages(conn, 'day'):
            csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
        
        csv.write('\nMonthly  messages\n')
        csv.write('Date, Messages\n')
        for r, d in get_xaily_messages(conn, 'month'):
            csv.write("%s, %.2f\n" % (d.date().isoformat(), r))

        csv.write('\nAverage messages per user per day\n')
        csv.write('Date, Messages\n')
        for r, d in get_avg_msgs_per_user_per_xay(conn, 'day'):
            csv.write("%s, %.2f\n" % (d.date().isoformat(), r))

        csv.write('\nAverage messages per user per month\n')
        csv.write('Date, Messages\n')
        for r, d in get_avg_msgs_per_user_per_xay(conn, 'month'):
            csv.write("%s, %.2f\n" % (d.date().isoformat(), r))

        csv.write('\nAverage groups per member\n')
        csv.write('Date, Groups\n')
        for r, d in get_avg_groups_per_member_xaily(conn, 'month'):
            csv.write("%s, %.2f\n" % (d.date().isoformat(), r))

        csv.write('\nGroups created by month\n')
        csv.write('Date, Groups\n')
        for r, d in get_groups_by_xau(conn, 'month'):
            csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
        
        csv.write('\nDaily active users\n')
        csv.write('Date, Active users\n')
        for r, d in get_xau(conn, 'day'):
            csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
        
        csv.write('\nMonthly active users\n')
        csv.write('Date, Active users\n')
        for r, d in get_xau(conn, 'month'):
            csv.write("%s, %.2f\n" % (d.date().isoformat(), r))