This repository has been archived by the owner on May 20, 2024. It is now read-only.
forked from vindimy/altcointip
-
Notifications
You must be signed in to change notification settings - Fork 30
/
Copy pathdb.yml
110 lines (109 loc) · 7.08 KB
/
db.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
auth:
user: 'mysqluser'
password: 'mysqlpass'
host: localhost
port: 3306
dbname: mysqldb
sql:
globalstats:
01_total_tipped_usd:
name: "Total Accepted Tips (USD)"
desc: "Total value of all tips given and accepted in USD (default) fiat"
type: line
query: "SELECT SUM(fiat_val) AS total_usd, fiat FROM t_action WHERE type = 'givetip' AND state = 'completed' AND fiat = 'usd'"
01a_total_tipped_usd_by_coin:
name: "Total Accepted Tips (USD) By Coin"
desc: "Total value of all tips given and accepted in USD (default) fiat grouped by coin"
type: table
query: "SELECT coin, SUM(fiat_val) AS total_usd, fiat FROM t_action WHERE type = 'givetip' AND state = 'completed' AND fiat = 'usd' GROUP BY coin ORDER BY coin"
02_total_tips_expired_and_declined:
name: "Total Expired and Declined Tips (USD)"
desc: "Total value of all tips given that weren't accepted (expired or declined) in USD (default) fiat"
type: line
query: "SELECT SUM(fiat_val) AS total_usd, fiat FROM t_action WHERE type = 'givetip' AND state = 'expired' OR state = 'declined' AND fiat = 'usd'"
03_total_users_registered:
name: "Total Users Registered"
desc: "Number of registered users"
type: line
query: "SELECT COUNT(username) AS total_users FROM t_users"
04_total_tippers:
name: "Total Tippers"
desc: "Number of users who tipped at least once"
type: line
query: "SELECT COUNT(from_user) AS total_tippers FROM (SELECT DISTINCT from_user FROM t_action WHERE type = 'givetip') AS t_distinct_action"
05_total_tips:
name: "Total Number of Tips"
desc: "Total number of tips given"
type: line
query: "SELECT COUNT(msg_id) AS total_tips FROM t_action WHERE type = 'givetip' AND state = 'completed'"
05a_total_tips_by_coin:
name: "Total Number of Tips (by coin)"
desc: "Total number of tips given grouped by coin"
type: table
query: "SELECT coin, count(*) AS total_tips FROM t_action WHERE type = 'givetip' AND state = 'completed' GROUP BY coin ORDER BY coin"
05b_total_karma_redeemed:
name: "Total Karma Redeemed (USD)"
desc: "Total value of redeemed karma"
type: line
query: "SELECT SUM(fiat_val) AS total_usd, fiat FROM t_action WHERE type = 'redeem' AND state = 'completed' AND fiat = 'usd'"
06_top_10_tippers:
name: "Top 10 Tippers"
desc: "Top 10 all-time tippers as determined by total USD/EUR (fiat) value of their tips."
type: table
query: "SELECT from_user, SUM(fiat_val) AS total_fiat, fiat FROM t_action WHERE type = 'givetip' AND state = 'completed' AND fiat IN ('usd', 'eur') GROUP BY from_user ORDER BY total_fiat DESC LIMIT 10"
07_top_10_tips:
name: "Top 10 Tips"
desc: "Top 10 all-time tips as determined by their USD/EUR (fiat) value."
type: table
query: "SELECT from_user, to_user, coin_val, coin, fiat_val, fiat, msg_link FROM t_action WHERE type ='givetip' AND state = 'completed' AND fiat IN ('usd', 'eur') ORDER BY fiat_val DESC LIMIT 10"
07a_top_5_tips_btc:
name: "Top 5 Tips (BTC)"
desc: "Top 5 all-time BTC tips as determined by coin amount"
type: table
query: "SELECT from_user, to_user, coin_val, coin, fiat_val, fiat, msg_link FROM t_action WHERE type ='givetip' AND state = 'completed' AND coin = 'btc' ORDER BY coin_val DESC LIMIT 5"
07b_top_5_tips_ltc:
name: "Top 5 Tips (LTC)"
desc: "Top 5 all-time LTC tips as determined by coin amount"
type: table
query: "SELECT from_user, to_user, coin_val, coin, fiat_val, fiat, msg_link FROM t_action WHERE type ='givetip' AND state = 'completed' AND coin = 'ltc' ORDER BY coin_val DESC LIMIT 5"
07c_top_5_tips_ppc:
name: "Top 5 Tips (PPC)"
desc: "Top 5 all-time PPC tips as determined by coin amount"
type: table
query: "SELECT from_user, to_user, coin_val, coin, fiat_val, fiat, msg_link FROM t_action WHERE type ='givetip' AND state = 'completed' AND coin = 'ppc' ORDER BY coin_val DESC LIMIT 5"
07d_top_5_tips_nmc:
name: "Top 5 Tips (NMC)"
desc: "Top 5 all-time NMC tips as determined by coin amount"
type: table
query: "SELECT from_user, to_user, coin_val, coin, fiat_val, fiat, msg_link FROM t_action WHERE type ='givetip' AND state = 'completed' AND coin = 'nmc' ORDER BY coin_val DESC LIMIT 5"
07e_top_5_tips_xpm:
name: "Top 5 Tips (XPM)"
desc: "Top 5 all-time XPM tips as determined by coin amount"
type: table
query: "SELECT from_user, to_user, coin_val, coin, fiat_val, fiat, msg_link FROM t_action WHERE type ='givetip' AND state = 'completed' AND coin = 'xpm' ORDER BY coin_val DESC LIMIT 5"
07f_top_5_tips_mec:
name: "Top 5 Tips (MEC)"
desc: "Top 5 all-time MEC tips as determined by coin amount"
type: table
query: "SELECT from_user, to_user, coin_val, coin, fiat_val, fiat, msg_link FROM t_action WHERE type ='givetip' AND state = 'completed' AND coin = 'mec' ORDER BY coin_val DESC LIMIT 5"
08_top_10_receivers:
name: "Top 10 Receivers"
desc: "Top 10 all-time tip receivers as determined by total USD/EUR (fiat) value of their received tips."
type: table
query: "SELECT to_user, SUM(fiat_val) AS total_fiat, fiat FROM t_action WHERE type = 'givetip' AND state = 'completed' AND fiat IN ('usd', 'eur') AND to_user IS NOT NULL GROUP BY to_user ORDER BY total_fiat DESC LIMIT 10"
userstats:
users: "SELECT username FROM t_users WHERE username IN (SELECT from_user FROM t_action WHERE type = 'givetip') OR username in (SELECT to_user FROM t_action WHERE type = 'givetip') ORDER BY username"
coins: 'SELECT DISTINCT coin FROM t_action WHERE coin IS NOT NULL ORDER BY coin'
fiat: 'SELECT DISTINCT fiat FROM t_action WHERE fiat IS NOT NULL ORDER BY fiat'
history: "SELECT from_user, to_user, created_utc, to_addr, coin_val, coin, fiat_val, fiat, state, subreddit, msg_link FROM t_action WHERE type='givetip' AND (from_user=%s OR to_user=%s) ORDER BY created_utc DESC"
total_tipped_fiat: "SELECT SUM(fiat_val) AS total_fiat FROM t_action WHERE type='givetip' AND state='completed' AND from_user=%s AND fiat=%s"
total_tipped_coin: "SELECT SUM(coin_val) AS total_coin FROM t_action WHERE type='givetip' AND state='completed' AND from_user=%s AND coin=%s"
total_received_fiat: "SELECT SUM(fiat_val) AS total_fiat FROM t_action WHERE type='givetip' AND state='completed' AND to_user=%s AND fiat=%s"
total_received_coin: "SELECT SUM(coin_val) AS total_coin FROM t_action WHERE type='givetip' AND state='completed' AND to_user=%s AND coin=%s"
userhistory:
sql: "SELECT type, state, from_user, to_user, created_utc, to_addr, coin_val, coin, fiat_val, fiat, subreddit FROM t_action WHERE type IN ('givetip', 'redeem', 'withdraw') AND (from_user=%s OR to_user=%s) ORDER BY created_utc DESC LIMIT %s"
limit: 75
tips:
sql_set: "SET @rank=0"
sql_list: "SELECT @rank :=@rank+1 AS num, created_utc, from_user, to_user, coin_val, coin, fiat_val, fiat, subreddit, msg_link FROM t_action WHERE type='givetip' AND state='completed' ORDER BY created_utc ASC LIMIT %s"
limit: 10000