declare @comment_length int
declare @comment_count int
SET @comment_length = (select sum(len(body)) from comments where depth > 0 and author = 'sumatranate')
SET @comment_count = (select count(*) from comments where depth > 0 and author = 'sumatranate')
SELECT @comment_length, '|',@comment_count, '|', @comment_length/@comment_count
select
sum(case when depth = 0 then 1 else 0 end) Post_count,
sum(case when depth > 0 then 1 else 0 end) Comment_count,
sum(case when depth > 0 then len(body) else 0 end) Comment_Length,
sum(case when depth > 0 then 1 else 0 end)/sum(case when depth = 0 then 1 else 0 end) as Comments_Per_post,
sum(case when depth > 0 then len(body) else 0 end)/sum(case when depth > 0 then 1 else 0 end) as Comm_len_avg
from comments where author = 'arcange'
Thanks man. I appreciate you pushing people toward engagement. How did u come up with 219 the other way? Just curious.
Posted using Partiko iOS
No worries :)
I use @steemsql and this dirty query:
This is really dirty query 😏
You're making 2 table scans to compute length and count where it could be done in 1
Sorry :)
Is this better?
!ENGAGE 100
:(
Sent you some directly :)
If's way better! You can even use the new IIF() function to make it more readable:
select sum(IIF(depth = 0, 1,0) AS Postcount, sum(IIF(depth > 0, 1, 0)AS Commentcount, sum(IIF(depth > 0, len(body), 0) AS CommentLength, sum(IIF(depth > 0, 1, 0))/sum(IF(depth = 0, 1, 0)) AS CommentsPerpost, sum(IIF(depth > 0, len(body), 0))/sum(IIF(depth > 0, 1,0) AS Commlen_avg from comments where author = 'arcange' ```<div class="pull-right"><sub><a href="/steemreply/@arcange/introducing-steemreply-stay-in-touch-with-your-steem-network">Posted with <img src="http://steemreply.com/logo-comment.png"/></a></sub></div>