def questions_with_highest_comments(count=20):
results = db.session.execute(
text("""SELECT
u.first_name as 'Asked to',
u.username as 'username',
concat('http://www.frankly.me/',u.username,'/',q.slug)
as question_link,
count(1) as 'No. of Comments'
from questions q
inner join posts p
on q.id = p.question
inner join comments c
on p.id = c.on_post
inner join users umonk
on c.comment_author = umonk.id
inner join users u
on u.id = q.question_to
left join testers t
on u.username = t.username
where
umonk.monkness = -1
and t.id IS NULL
and c.deleted = false
and p.deleted = false
group by q.id
order by count(1) desc
limit :count ;
"""),
params={'count' : count}
)
resp = make_html_table(results)
return resp
def users_with_highest_increase_in_follows(count=20):
results = db.session.execute(
text("""SELECT
u.first_name as'Name',
u.username,
u.email,
u.phone_num as 'Phone'
from users u
inner join user_follows uf
on u.id = uf.followed
inner join users umonk
on uf.user = umonk.id
left join testers t
on u.username = t.username
where
umonk.monkness = -1
and t.id IS NULL
and u.monkness = -1
and uf.timestamp >= now() -interval 1 day
and uf.unfollowed = false
group by u.id
order by count(1) desc
limit :count
"""),
params={'count' : count}
)
resp = make_html_table(results)
return resp
def questions_with_most_upvotes(count=20):
results = db.session.execute(
text("""SELECT
u.first_name as 'Asked to',
u.username as 'username',
concat('http://www.frankly.me/',u.username,'/',q.slug)
as question_link
from questions q
inner join question_upvotes qu
on q.id = qu.question
inner join users umonk
on qu.user = umonk.id
inner join users u
on u.id = q.question_to
left join testers t
on u.username = t.username
where
umonk.monkness = -1
and t.id IS NULL
and qu.downvoted = false
group by q.id
order by count(1) desc
limit :limit
"""),
params={'limit' : count}
)
resp = make_html_table(results)
return resp
def users_being_asked_questions_more_than_or_equal_to(count=3):
results = db.session.execute(
text("""SELECT u.first_name as'Name' ,u.username, u.email,
u.phone_num as 'Phone'
from users u
inner join questions q on u.id = q.question_to
left join testers t on u.username = t.username
where q.timestamp > now() - interval 1 day
and q.added_by IS NULL
and t.id IS NULL
and u.monkness = -1
group by u.id having count(1) >= :count
"""),
params={'count' : count}
)
resp = make_html_table(results)
return resp
def new_registrations(since_hours=12):
"""Fetches a report of new registrations for stats email.
Args:
since_hours: Time interval since when the report is to be generated.
Returns:
An HTML Table of results.
"""
results = db.session.execute(
text("""SELECT u.user_type, u.registered_with, count(u.username)
FROM users u
WHERE u.user_since > date_sub(now(), interval :since_hours hour)
AND u.monkness = -1
GROUP BY u.user_type, u.registered_with;
"""),
params={'since_hours':since_hours}
)
resp = make_html_table(results)
return resp
def question_askers_for_sapru():
results = db.session.execute(
text("""SELECT
u.first_name,
u.email,
u.phone_num,
DATE_FORMAT(q.timestamp,"%D %M") as date,
count(*) as q_count
from questions q
left join users u
on q.question_author = u.id
where
q.question_to = '737c6f8a7ac04d7e9380f1d37c011531'
and u.monkness = -1
group by q.question_author
order by q_count DESC;
"""),
params={}
)
return make_html_table(results)
def videos_uploaded(since_hours):
"""Fetches a report of new video uploads.
Args:
since_hours: Time interval since when the report is to be generated.
Returns:
An HTML Table of results.
"""
results = db.session.execute(
text("""SELECT v.username, v.url ,u.user_type
from videos v
inner join users u on v.username = u.username
left join testers t on v.username = t.username
where u.monkness = -1
and t.id IS NULL
and v.delete = false
and v.created_at > date_sub(now(), interval :since_hours hour);
"""),
params={'since_hours':since_hours}
)
resp = make_html_table(results)
return resp
def users_asking_questions_more_than_or_equal_to(count=3):
"""Fetches users asking questions more than or equal to specified each day.
Args:
count: asked question count.
Returns:
An HTML Table of results.
"""
results = db.session.execute(
text("""SELECT u.first_name as'Name' ,u.username, u.email,
u.phone_num as 'Phone'
from users u
inner join questions q on u.id = q.question_author
left join testers t on u.username = t.username
where q.timestamp > now() - interval 1 day
and t.id IS NULL
and u.monkness = -1
group by u.id having count(1) >= :count;
"""),
params={'count' : count}
)
resp = make_html_table(results)
return resp
def count_of_question_asked(since_hours=12):
"""Fetches a report of number of questions asked for each user type.
Args:
since_hours: Time interval since when the report is to be generated.
Returns:
An HTML Table of results.
"""
results = db.session.execute(
text("""SELECT u.user_type , count(q.id) as 'No. of questions'
from users u
inner join questions q on q.question_to = u.id
left join testers t on u.username = t.username
where u.monkness = -1
and t.id IS NULL
and q.deleted = false
and q.added_by IS NULL
and q.timestamp > date_sub(now(), interval :since_hours hour);
"""),
params={'since_hours':since_hours}
)
resp = make_html_table(results)
return resp
