0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for administrators, end users, developers and designers for Craft CMS

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 '%I have the same problem%' then 1 else 0 end +
    CASE WHEN body LIKE '%I have the exact 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 '%does anybody know%' then 1 else 0 end +
    CASE WHEN body LIKE '%please help%' then 1 else 0 end +
    CASE WHEN body LIKE '%!!%' or body LIKE '%??%' then 1 else 0 end +
    CASE WHEN body LIKE '%doesnt work%' then 1 else 0 end +
    CASE WHEN body LIKE '%what to do if%' then 1 else 0 end +
    CASE WHEN LEN(Body) < 100 then 1 else 0 end +
    CASE WHEN score > 5 then -1 else 0 end
    ),
  Score,
  Body,
  CreationDate
FROM Posts
WHERE PostTypeId = 2 and LEN(Body) < 100
GROUP BY Id, Score, Body, CreationDate
ORDER BY NAAscore DESC;

fork query

edited dec 3 anonymous

created nov 29 Starship