SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Scary's On The Wall
    Join Date
    Apr 2003
    Location
    PA
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complicated Select Statement

    Hi MySQLers,

    I recently added a random security check to my site to make sure the people who were on the site were actually real people and not bots. To pass the security check, the user must enter the letter/number combination they see on an image. My next step is to create a simple ranking of who is most likely to be using a bot. I figure the higher percentage of security check failures, the more likely it is that user is using a bot.

    My Table Info (for reference)
    Code:
    Table Name: security_checks
    Fields: check_id (medint), user_id (varchar), result(varchar), time(varchar)
    
    'check_id' is the primary, auto-incremental field
    'user_id' is the id of the user being logged
    'result' is the result of the security check, either 'passed' or 'failed'
    'time' is the unix timestamp of when it was logged
    Right now, I have one MySQL query to list the people who have the highest number of failures:

    Code:
    SELECT user_id, time, COUNT(*) AS 'failed'
    FROM security_checks
    WHERE result = 'failed'
    GROUP BY user_id
    ORDER BY failed DESC
    This is okay, but its not perfect. What I would love to do is have one query that would select the following:

    user_id
    time
    Number of Successes
    Number of Failures
    Percent Failures (Derived from the above 2)
    ... and grouped by user_id
    ... and ordered By Percent Failures (desc)

    Now I have tried a million ways of doing this, but I am just not experienced enough in MySQL to do it. Could anyone help me out, or at least point me in the right direction? Thank you very much in advance, I appreciate it so much!

    P.S. If you're really feeling smart, see if you can also join fields connected through user_id from the users table, such as user_name
    Last edited by Haleden; Feb 15, 2005 at 19:48. Reason: Needed Title Change

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select S.user_id
         , U.user_name
         , max(S.time)                     as latest_attempt
         , sum(case when S.result = 'failed'
                    then 1 else 0 end)   as Number_of_Failures
         , count(*)
          -sum(case when S.result = 'failed'
                    then 1 else 0 end)   as Number_of_Successes
         , 100.0
          *sum(case when S.result = 'failed'
                    then 1 else 0 end) 
          /count(*)                      as Percent_Failures 
      from security_checks as S
    inner
      join users as U
        on S.user_id = U.user_id  
    group 
        by S.user_id
         , U.user_name
    order 
        by Percent_Failure desc
    Last edited by r937; Feb 16, 2005 at 06:25.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Scary's On The Wall
    Join Date
    Apr 2003
    Location
    PA
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol! Oh my god...

    *bows down before god*

    Thank you! That is one crazy statement.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •