CODEWITHSUNDEEP
sqlsqlite
Marduk
Marduk

Reputation: 389

Getting inactive users from database

I have SQL database dump from some forum. I'm trying to build query which will return me a list of unactive users that didn't send any post from like one year. I'm thinking about using subquery with last post but i'm not sure if i'm right. For now I didn't manage to get any possible results... Can you help me guys?

post_database:

id  INTEGER                         
topic_id    INTEGER                         
post_id INTEGER                         
user_id INTEGER                         
date    TEXT                            
content TEXT

user_database:

id  INTEGER                         
name    TEXT

Upvotes: 0

Views: 750

Answers (1)

Hart CO
Hart CO

Reputation: 34774

I don't know much about SQLite, but you want to GROUP BY some fields and use a HAVING clause to limit to those who haven't posted in the past year:

SELECT u.name
FROM post_database AS p
JOIN user_database AS u
  ON p.user_id = u.id
GROUP BY u.name
HAVING MAX(date) < DATE('now','-1 year')

I'm not sure about the date comparison portion.

Upvotes: 1

Related Questions