0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for developers building blockchains with the Substrate SDK

SELECT top 100
  Id AS [Post Link],
  NAAscore = 
    SUM(
    CASE WHEN body LIKE '%thank%' then 1 else 0 end +
    CASE WHEN body LIKE '%thx%' then 1 else 0 end +
    CASE WHEN body LIKE '%appreciated%' then 1 else 0 end +
    CASE WHEN body LIKE '%regards%' then 1 else 0 end +
    CASE WHEN body LIKE '%?%' then 1 else 0 end +
    CASE WHEN body LIKE '%please provide%' then 1 else 0 end +
    CASE WHEN body LIKE '%can you please%' then 1 else 0 end +
    CASE WHEN body LIKE '%can I do%' then 1 else 0 end +
    CASE WHEN body LIKE '%help me%' then 1 else 0 end +
    CASE WHEN body LIKE '%can you tell me%' then 1 else 0 end +
    CASE WHEN body LIKE '%how do you%' then 1 else 0 end +
    CASE WHEN body LIKE '%can you give me%' then 1 else 0 end +
    CASE WHEN body LIKE '%did you manage to%' then 1 else 0 end +
    CASE WHEN body LIKE '%how can I%' then 1 else 0 end +
    CASE WHEN body LIKE '%I need%' then 1 else 0 end +
    CASE WHEN body LIKE '%any solution%' then 1 else 0 end +
    CASE WHEN body LIKE '%how can we%' then 1 else 0 end +
    CASE WHEN body LIKE '%have you found a solution%' then 1 else 0 end +
    CASE WHEN body LIKE '%save my day%' then 1 else 0 end +
    CASE WHEN body LIKE '%can you explain%' then 1 else 0 end +
    CASE WHEN body LIKE '%we have the same problem%' then 1 else 0 end +
    CASE WHEN body LIKE '%please give me%' then 1 else 0 end +
    CASE WHEN body LIKE '%its not working%' then 1 else 0 end +
    CASE WHEN body LIKE '%solve this%' then 1 else 0 end +
    CASE WHEN body LIKE '%amazing%' then 1 else 0 end +
    CASE WHEN body LIKE '%please explain%' then 1 else 0 end +
    CASE WHEN body LIKE '%we have the same problem%' then 1 else 0 end +
    CASE WHEN body LIKE '%did you solve%' then 1 else 0 end +
    CASE WHEN body LIKE '%I am facing the same problem%' then 1 else 0 end +
    CASE WHEN body LIKE '%please elaborate%' then 1 else 0 end +
    CASE WHEN body LIKE '%update on this%' then 1 else 0 end +
    CASE WHEN body LIKE '%please help%' then 1 else 0 end
    ),
  Score,
  Body,
  CreationDate
FROM Posts
WHERE LEN(Body) > 179 and LEN(Body) <195 and PostTypeId = 2
GROUP BY Id, Score, Body, CreationDate
ORDER BY NAAscore DESC;

fork query

edited nov 29

created nov 29 Starship