0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for experienced flyers, pilots, and builders of drones and model aircraft

SELECT top 100
  Id AS [Post Link],
  NAAscore = 
    SUM(
    CASE WHEN body LIKE '%thank%' then 1.5 else 0 end +
    CASE WHEN body LIKE '%thx%' then 1.5 else 0 end +
    CASE WHEN body LIKE '%appreciated%' then 1.5 else 0 end +
    CASE WHEN body LIKE '%regards%' then 1.5 else 0 end +
    CASE WHEN body LIKE '%?%' then 1 else 0 end +
    CASE WHEN body LIKE '%please provide%' then 2 else 0 end +
    CASE WHEN body LIKE '%can you please%' then 2 else 0 end +
    CASE WHEN body LIKE '%can I do%' then 2 else 0 end +
    CASE WHEN body LIKE '%help me%' then 2 else 0 end +
    CASE WHEN body LIKE '%can you tell me%' then 2 else 0 end +
    CASE WHEN body LIKE '%how do you%' then 2 else 0 end +
    CASE WHEN body LIKE '%can you give me%' then 2 else 0 end +
    CASE WHEN body LIKE '%did you manage to%' then 2 else 0 end +
    CASE WHEN body LIKE '%how can I%' then 2 else 0 end +
    CASE WHEN body LIKE '%I need%' then 2 else 0 end +
    CASE WHEN body LIKE '%any solution%' then 2 else 0 end +
    CASE WHEN body LIKE '%how can we%' then 2 else 0 end +
    CASE WHEN body LIKE '%have you found a solution%' then 2 else 0 end +
    CASE WHEN body LIKE '%save my day%' then 2 else 0 end +
    CASE WHEN body LIKE '%can you explain%' then 2 else 0 end +
    CASE WHEN body LIKE '%we have the same problem%' then 2 else 0 end +
    CASE WHEN body LIKE '%please give me%' then 2 else 0 end +
    CASE WHEN body LIKE '%its not working%' then 2 else 0 end +
    CASE WHEN body LIKE '%solve this%' then 2 else 0 end +
    CASE WHEN body LIKE '%amazing%' then 2 else 0 end +
    CASE WHEN body LIKE '%please explain%' then 2 else 0 end +
    CASE WHEN body LIKE '%I have the same problem%' then 2 else 0 end +
    CASE WHEN body LIKE '%Im having the same problem%' then 2 else 0 end +
    CASE WHEN body LIKE '%I have the exact same problem%' then 2 else 0 end +
    CASE WHEN body LIKE '%did you solve%' then 2 else 0 end +
    CASE WHEN body LIKE '%I am facing the same problem%' then 2 else 0 end +
    CASE WHEN body LIKE '%please elaborate%' then 2 else 0 end +
    CASE WHEN body LIKE '%update on this%' then 2 else 0 end +
    CASE WHEN body LIKE '%does anybody know%' then 2 else 0 end +
    CASE WHEN body LIKE '%please help%' then 2 else 0 end +
    CASE WHEN body LIKE '%!!%' or body LIKE '%??%' then 1 else 0 end +
    CASE WHEN body LIKE '%doesnt work%' then 2 else 0 end +
    CASE WHEN body LIKE '%what to do if%' then 2 else 0 end +
    CASE WHEN body LIKE '%can someone help%' then 2 else 0 end +
    CASE WHEN body LIKE '%I am facing issue%' then 2 else 0 end +
    CASE WHEN body LIKE '%I am also facing the same issue%' then 2 else 0 end +
    CASE WHEN body LIKE '%your help%' then 2 else 0 end +
    CASE WHEN body LIKE '%any help would%' then 2 else 0 end +
    CASE WHEN body LIKE '%you saved my%' then 2 else 0 end +
    CASE WHEN body LIKE '%did you figure%' then 2 else 0 end +
    CASE WHEN body LIKE '%I would like to know%' then 2 else 0 end +
    CASE WHEN body LIKE '%what am I doing wrong%' then 2 else 0 end +
    CASE WHEN body LIKE '%were you able to figure out%' then 2 else 0 end +
    CASE WHEN body LIKE '%this blog%' then 2 else 0 end +
    CASE WHEN body LIKE '%but not working%' then 2 else 0 end +
    CASE WHEN body LIKE '%I have same problem%' then 2 else 0 end +
    CASE WHEN body LIKE '%this guide%' then 2 else 0 end +
    CASE WHEN body LIKE '%saved my day%' then 2 else 0 end +
    CASE WHEN body LIKE '%please tell me%' then 2 else 0 end +
    CASE WHEN body LIKE '%this article%' then 2 else 0 end +
    CASE WHEN body LIKE '%did not work%' then 2 else 0 end +
    CASE WHEN body LIKE '%Im having the same problem%' then 2 else 0 end +
    CASE WHEN body LIKE '%I am trying to%' then 2 else 0 end +
    CASE WHEN body LIKE '%can I do%' then 2 else 0 end +
    CASE WHEN body LIKE '%this document%' then 2 else 0 end +
    CASE WHEN body LIKE '%please show%' then 2 else 0 end +
    CASE WHEN body LIKE '%could you please%' then 2 else 0 end +
    CASE WHEN body LIKE '%how do I%' then 2 else 0 end +
    CASE WHEN body LIKE '%my question%' then 2 else 0 end +
    CASE WHEN body LIKE '%but nothing work%' then 2 else 0 end +
    CASE WHEN body LIKE '%I am not able to%' then 2 else 0 end +
    CASE WHEN body LIKE '%is there a solution%' then 2 else 0 end +
    CASE WHEN body LIKE '%you can%' then -1 else 0 end +
    CASE WHEN body LIKE '%I had the same issue%' then -1 else 0 end +
    CASE WHEN body LIKE '%try to%' then -1 else 0 end +
    CASE WHEN body LIKE '%I needed to%' then -1 else 0 end +
    CASE WHEN body LIKE '%I fixed%' then -1 else 0 end +
    CASE WHEN body LIKE '%workaround%' then -1 else 0 end +
    CASE WHEN body LIKE '%surely will help%' then -1 else 0 end +
    CASE WHEN body LIKE '%try using%' then -1 else 0 end +
    CASE WHEN body LIKE '%try the%' then -1 else 0 end +
    CASE WHEN body LIKE '%this worked for me%' then -1 else 0 end +
    CASE WHEN body LIKE '%will work%' then -1 else 0 end +
    CASE WHEN body LIKE '%why dont you%' then -1 else 0 end +
    CASE WHEN body LIKE '%how about%' then -1 else 0 end +
    CASE WHEN body LIKE '%what about using%' then -1 else 0 end +
    CASE WHEN body LIKE '%hope this helps%' then -1 else 0 end +
    CASE WHEN body LIKE '%did you try%' then -1 else 0 end +
    CASE WHEN body LIKE '%solved it by%' then -1 else 0 end +
    CASE WHEN body LIKE '%was the solution%' then -1 else 0 end +
    CASE WHEN body LIKE '%why not%' then -1 else 0 end +
    CASE WHEN body LIKE '%?</p>%' and body not LIKE '%?</p> <p>%' then 1 else 0 end +
    CASE WHEN body LIKE '%<p>why%' and body not LIKE '%</p> <p>why%' then 1.5 else 0 end +
    CASE WHEN body LIKE '%<p>can%' and body not LIKE '%</p> <p>can%' then 1.5 else 0 end +
    CASE WHEN body LIKE '%<p>same%' and body not LIKE '%</p> <p>same%' then 1.5 else 0 end +
    CASE WHEN body LIKE '%<p>where%' and body not LIKE '%</p> <p>where%' then 1.5 else 0 end +
    CASE WHEN body LIKE '%<p>what%' and body not LIKE '%</p> <p>what%' then 1.5 else 0 end +
    CASE WHEN body not LIKE '%</p>%' then 0.5 else 0 end +
    CASE WHEN body LIKE '%<p>@%' and body not LIKE '%</p> <p>@%' then 1 else 0 end +
    CASE WHEN body not LIKE '%<code>%' then 0.5 else 0 end +
    CASE WHEN LEN(Body) < 500 then 0.5 else 0 end +
    CASE WHEN LEN(Body) < 250 then 0.5 else 0 end +
    CASE WHEN LEN(Body) < 100 then 0.5 else 0 end +
    CASE WHEN LEN(Body) > 499 then -0.5 else 0 end +
    CASE WHEN score > 0 then -1 else 0 end
    ),
  Score,
  Body,
  CreationDate
FROM Posts
WHERE PostTypeId = 2 and LEN(Body) < 500 and CreationDate BETWEEN '2024-11-10' AND '2024-12-19' and LEN(BODY) < 100
GROUP BY Id, Score, Body, CreationDate
ORDER BY NAAscore DESC;

fork query

edited jan 3

created nov 29 Starship