Forum:Sql Query For Badges

Question

[Istvan Albert]

Calling on all SQL novices and gurus to help us create queries to award badges. We're looking for queries that return a set of users that qualify for a badge.

An typical query selects users by one condition and excludes users that already have that award. If the query gets too long/slow it is acceptable to do the exclusion in a separate step.

Here are the relevant sections of the schemas:

BEGIN;
CREATE TABLE auth_user (
    id integer NOT NULL,
    username character varying(30) NOT NULL,
    first_name character varying(30) NOT NULL,
    last_name character varying(30) NOT NULL,
    email character varying(75) NOT NULL,
    password character varying(128) NOT NULL,
    is_staff boolean NOT NULL,
    is_active boolean NOT NULL,
    is_superuser boolean NOT NULL,
    last_login timestamp with time zone NOT NULL,
    date_joined timestamp with time zone NOT NULL
);
CREATE TABLE "server_userprofile" (
    "id" integer NOT NULL PRIMARY KEY,
    "user_id" integer NOT NULL UNIQUE REFERENCES "auth_user" ("id"),
    "display_name" varchar(250) NOT NULL,
    "type" integer NOT NULL,
    "uuid" text NOT NULL UNIQUE,
    "score" integer NOT NULL,
    "bronze_badges" integer NOT NULL,
    "silver_badges" integer NOT NULL,
    "gold_badges" integer NOT NULL,
    "new_messages" integer NOT NULL,
    "last_visited" datetime NOT NULL,
    "status" integer NOT NULL,
    "about_me" text,
    "about_me_html" text,
    "location" text,
    "website" varchar(250),
    "my_tags" text,
    "scholar" text
)
;
CREATE TABLE "server_tag" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" text NOT NULL,
    "count" integer NOT NULL
)
;
CREATE TABLE "server_post_tag_set" (
    "id" integer NOT NULL PRIMARY KEY,
    "post_id" integer NOT NULL,
    "tag_id" integer NOT NULL REFERENCES "server_tag" ("id"),
    UNIQUE ("post_id", "tag_id")
)
;
CREATE TABLE "server_post" (
    "id" integer NOT NULL PRIMARY KEY,
    "author_id" integer NOT NULL REFERENCES "auth_user" ("id"),
    "content" text NOT NULL,
    "html" text NOT NULL,
    "title" text NOT NULL,
    "slug" varchar(200) NOT NULL,
    "tag_val" varchar(200) NOT NULL,
    "views" integer NOT NULL,
    "score" integer NOT NULL,
    "full_score" integer NOT NULL,
    "creation_date" datetime NOT NULL,
    "lastedit_date" datetime NOT NULL,
    "lastedit_user_id" integer NOT NULL REFERENCES "auth_user" ("id"),
    "status" integer NOT NULL,
    "type" integer NOT NULL,
    "root_id" integer,
    "parent_id" integer,
    "comment_count" integer NOT NULL,
    "revision_count" integer NOT NULL,
    "answer_count" integer NOT NULL,
    "accepted" bool NOT NULL,
    "url" varchar(200) NOT NULL,
    "rank" real NOT NULL
)
;
CREATE TABLE "server_blog" (
    "id" integer NOT NULL PRIMARY KEY,
    "author_id" integer NOT NULL REFERENCES "auth_user" ("id"),
    "url" varchar(500) NOT NULL
)
;
CREATE TABLE "server_postrevision" (
    "id" integer NOT NULL PRIMARY KEY,
    "post_id" integer NOT NULL REFERENCES "server_post" ("id"),
    "diff" text NOT NULL,
    "content" text NOT NULL,
    "author_id" integer NOT NULL REFERENCES "auth_user" ("id"),
    "date" datetime NOT NULL
)
;
CREATE TABLE "server_vote" (
    "id" integer NOT NULL PRIMARY KEY,
    "author_id" integer NOT NULL REFERENCES "auth_user" ("id"),
    "post_id" integer NOT NULL REFERENCES "server_post" ("id"),
    "type" integer NOT NULL,
    "date" datetime NOT NULL
)
;
CREATE TABLE "server_badge" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(50) NOT NULL,
    "description" varchar(200) NOT NULL,
    "type" integer NOT NULL,
    "unique" bool NOT NULL,
    "secret" bool NOT NULL,
    "count" integer NOT NULL
)
;
CREATE TABLE "server_award" (
    "id" integer NOT NULL PRIMARY KEY,
    "badge_id" integer NOT NULL REFERENCES "server_badge" ("id"),
    "user_id" integer NOT NULL REFERENCES "auth_user" ("id"),
    "date" datetime NOT NULL
)
;
COMMIT;

[Roger]

Do you still need an help about this topic?

[Istvan Albert]

yes, this is still an active field of discussion, queries in Django ORM form are here:

https://github.com/ialbert/biostar-central/blob/master/main/server/awards.py

but you can formulate them in real SQL as well