{"descr":"A database for a viral mailer website that combines a viral mailer, a banner exchange, and a text ad exchange","userinfo":"Search for information about credits, banners in rotation, mailing logs, and other mailer-related topics","tables":{"oto_mbanners":{"descr":"The list of banner ads in the exchange","columns":{"id":{"type":"int, Primary Key","descr":"Banner ID"},"state":{"type":"tinyint","descr":"Status 0=\"Pending\", 1=\"enabled\", 2=\"paused by member\", 3=\"suspended by admin\""},"memid":{"type":"int","descr":"The User ID who submitted the banner"},"img":{"type":"varchar","descr":"The image URL of the banner"},"target":{"type":"varchar","descr":"The target URL of the banner"},"imps":{"type":"int","descr":"Number of impressions currently assigned to the banner"},"hits":{"type":"int","descr":"Number of impressions the banner received"},"clicks":{"type":"int","descr":"Number of clicks the banner received"}}},"oto_members":{"descr":"List of users","columns":{"Id":{"type":"int, Primary Key","descr":"user ID"},"username":{"type":"varchar","descr":""},"firstname":{"type":"varchar","descr":""},"lastname":{"type":"varchar","descr":""},"email":{"type":"varchar","descr":""},"refid":{"type":"int","descr":"referrer ID (0 if direct join)"},"status":{"type":"varchar","descr":"Status of the account (Active, Unverified, or Suspended)"},"joindate":{"type":"datetime","descr":""},"mtype":{"type":"int","descr":"Number representing the user account level (This number matches mtid in the oto_membertypes table)"},"lastlogin":{"type":"datetime","descr":"The datetime the user last logged in"},"numLogins":{"type":"int","descr":"Number of times the user has logged in"},"credits":{"type":"float","descr":"User's credits balance"},"bannerimps":{"type":"int","descr":"User's banner impression balance"},"textimps":{"type":"int","descr":"User's text impression balance"},"lastmailing":{"type":"int","descr":"Unix timestamp of the last time the user sent a mailing"},"goodclicks":{"type":"int","descr":"Total number of times user clicked correct captcha icon"},"badclicks":{"type":"int","descr":"Total number of times user clicked incorrect captcha icon"},"clickstoday":{"type":"int","descr":"Number of credit links the user has clicked today"},"upgend":{"type":"date","descr":"The date the user's paid account level expires (YYYY-MM-DD, 0000-00-00 for lifetime membership)"},"mailrectoday":{"type":"int","descr":"Number of mailings the user received today"}}},"oto_membertypes":{"descr":"List of membership levels","columns":{"mtid":{"type":"int, Primary Key","descr":"Membership type ID number"},"accname":{"type":"varchar","descr":"Name of the membership level"},"surfratio":{"type":"float","descr":"Amount of credit user receives per credit link clicked"},"surftimer":{"type":"tinyint","descr":"Number of seconds user must view the site before earning credit"},"maxbanners":{"type":"int","descr":"Max number of banners user can promote"},"maxtexts":{"type":"int","descr":"Max number of text ads user can promote"},"monthcrds":{"type":"int","descr":"Number of monthly credits user receives from their upgrade"},"monthbimps":{"type":"int","descr":"Number of monthly banner impressions user receives from their upgrade"},"monthtimps":{"type":"int","descr":"Number of monthly text impressions user receives from their upgrade"},"mailhours":{"type":"int","descr":"Number of hours the user must wait between sending mailings"},"maillimit":{"type":"int","descr":"Max number of credits the user can spend per mailing"},"maxmailings":{"type":"int","descr":"Max number of mailings the user will receive from other members in a day"}}},"oto_mtexts":{"descr":"List of text ads being promoted","columns":{"id":{"type":"int, Primary Key","descr":"Text ad ID"},"state":{"type":"tinyint","descr":"Status 0=\"Pending\", 1=\"enabled\", 2=\"paused by member\", 3=\"suspended by admin\""},"memid":{"type":"int","descr":"User ID that submitted the text ad"},"text":{"type":"varchar","descr":"The text of the ad"},"target":{"type":"varchar","descr":"The target URL of the ad"},"imps":{"type":"int","descr":"Number of impressions currently assigned to the ad"},"hits":{"type":"int","descr":"Number of times the ad has been displayed"},"clicks":{"type":"int","descr":"Number of times the ad has been clicked"}}},"oto_prizelog":{"descr":"Log of prizes or rewards that members have received","columns":{"id":{"type":"int, Primary Key","descr":"Log entry ID"},"plugin":{"type":"varchar","descr":"The game or other source of the prize"},"user":{"type":"int","descr":"The User ID who won the prize"},"time":{"type":"int","descr":"The Unix timestamp when the prize was awarded"},"credits":{"type":"int","descr":"Number of credits in prize"},"bannerimps":{"type":"int","descr":"Number of banner impressions in prize"},"textimps":{"type":"int","descr":"Number of text ad impressions in prize"},"cash":{"type":"decimal","descr":"Amount of cash in prize"},"peelads":{"type":"int","descr":"Number of peel ad impressions in prize"},"sqbans":{"type":"int","descr":"Number of square banner impressions in prize"},"mbrands":{"type":"int","descr":"Number of mini brander impressions in prize"},"porkyps":{"type":"int","descr":"Number of Porky Points in prize"},"xppoints":{"type":"int","descr":"Number of XP points in prize"},"podpoints":{"type":"int","descr":"Number of pod points in prize"},"widgetads":{"type":"int","descr":"Number of widget ad impressions in prize"},"piggybank":{"type":"decimal","descr":"Amount of account balance cash in prize"},"notes":{"type":"varchar","descr":""}}},"oto_senthistory":{"descr":"The long-term stats for sent mailings. Stats are moved from oto_sentmessages to oto_senthistory after a few days.","columns":{"id":{"type":"int, Primary Key","descr":"Log ID"},"messageid":{"type":"int","descr":"Message ID"},"timesent":{"type":"int","descr":"Unix timestamp of when the mailing was sent"},"senderid":{"type":"int","descr":"User ID who sent the mailing"},"subjecttext":{"type":"varchar","descr":"Subject text of the mailing"},"sentto":{"type":"int","descr":"Number of users who received the mailing"},"numclicks":{"type":"int","descr":"Number of users who clicked the credit link"}}},"oto_sentmessages":{"descr":"A list of currently queued and recently sent mailings","columns":{"id":{"type":"int, Primary Key","descr":"Message ID"},"timesent":{"type":"int","descr":"Unix timestamp of when the mailing was sent"},"senderid":{"type":"int","descr":"User ID who sent the mailing"},"subjecttext":{"type":"varchar","descr":"Subject text of the mailing"},"bodytext":{"type":"text","descr":"Body text of the mailing"},"linkurl":{"type":"varchar","descr":"URL of the site members see after clicking the credit link"},"sendto":{"type":"int","descr":"Number of members the mailing will be sent to. This is higher than sentto while message is being sent."},"sentto":{"type":"int","descr":"Number of users who received the mailing"},"numclicks":{"type":"int","descr":"Number of users who clicked the credit link"}}},"oto_sstats":{"descr":"Log of the credit links clicked (last 7 days)","columns":{"id":{"type":"int, Primary Key","descr":"Log entry ID"},"userid":{"type":"varchar","descr":"The member who clicked the credit link"},"messageid":{"type":"int","descr":"ID of the mailing the credit link was for"},"url":{"type":"varchar","descr":"URL of the site that was shown"},"adate":{"type":"int","descr":"Unix timestamp when the link was clicked"}}},"oto_surflogs":{"descr":"History logs at the site (updated daily at midnight)","columns":{"id":{"type":"int, Primary Key","descr":"Log entry ID"},"date":{"type":"date","descr":"The date of the log entry"},"userid":{"type":"int","descr":"The user ID the stats are for"},"clicks":{"type":"int","descr":"Number of credit links the user clicked that day"},"time":{"type":"int","descr":"Number of seconds the user spent clicking credit links"},"messsent":{"type":"int","descr":"Number of messages the user sent"},"mailrec":{"type":"int","descr":"Number of mailings the user received"},"bandeliver":{"type":"int","descr":"The number of times the user\u2019s banners were shown that day"},"textdeliver":{"type":"int","descr":"The number of times the user\u2019s text ads were shown that day"}}}},"relationships":[{"relationship1":"oto_mbanners.memid","relationship2":"oto_members.Id"},{"relationship1":"oto_mtexts.memid","relationship2":"oto_members.Id"},{"relationship1":"oto_prizelog.user","relationship2":"oto_members.Id"},{"relationship1":"oto_sstats.userid","relationship2":"oto_members.Id"},{"relationship1":"oto_surflogs.userid","relationship2":"oto_members.Id"},{"relationship1":"oto_members.mtype","relationship2":"oto_membertypes.mtid"},{"relationship1":"oto_sentmessages.id","relationship2":"oto_sstats.messageid"},{"relationship1":"oto_sentmessages.senderid","relationship2":"oto_members.Id"},{"relationship1":"oto_senthistory.senderid","relationship2":"oto_members.Id"}],"training":[{"message":"How much cash has been given out in prizes over the last 30 days?","response":"SELECT COALESCE(SUM(cash), 0) AS total_cash FROM oto_prizelog WHERE FROM_UNIXTIME(time) >= DATE_SUB(NOW(), INTERVAL 30 DAY);","exampleshow":1,"exampletitle":"Get Prize Stats"},{"message":"List each banner ad along with the CTR","response":"SELECT id, img, target, clicks, hits, COALESCE((clicks\/hits*100), 0) AS CTR FROM oto_mbanners;","exampleshow":1,"exampletitle":"Get Banner Stats"},{"message":"How many credit links has User #1 clicked since May 1, 2023?","response":"SELECT COALESCE(SUM(clicks), 0) + COALESCE(clickstoday, 0) AS total_clicks FROM oto_surflogs JOIN oto_members ON oto_surflogs.userid = oto_members.Id WHERE oto_surflogs.date >= '2023-05-01' AND oto_members.Id = 1;","exampleshow":1,"exampletitle":"Get Credit Link Stats"},{"message":"How many credits does user Id #2's get when clicking on a credit link?","response":"SELECT oto_membertypes.surfratio FROM oto_members JOIN oto_membertypes ON oto_members.mtype = oto_membertypes.mtid WHERE oto_members.Id = 2;","exampleshow":1,"exampletitle":"Get a user's membership benefits"},{"message":"List all the banners that are in rotation right now","response":"SELECT id, img, target FROM oto_mbanners WHERE state = 1 AND imps >= 1;","exampleshow":1,"exampletitle":"See what is rotating in your mailer"},{"message":"List the top 5 members who won the most cash prizes in April 2023","response":"SELECT oto_members.Id, oto_members.username, COALESCE(SUM(oto_prizelog.cash), 0) AS total_cash, COUNT(oto_prizelog.id) AS total_prizes FROM oto_prizelog JOIN oto_members ON oto_prizelog.user = oto_members.Id WHERE oto_prizelog.cash > 0 AND YEAR(FROM_UNIXTIME(oto_prizelog.time)) = 2023 AND MONTH(FROM_UNIXTIME(oto_prizelog.time)) = 4 GROUP BY oto_members.Id ORDER BY total_cash DESC, total_prizes DESC LIMIT 5;","exampleshow":1,"exampletitle":"See which members are winning the most prizes"},{"message":"How many times has User ID #1 sent a mailing with the subject \"Get More Traffic\" since January 1, 2023?","response":"SELECT COUNT(*) AS total_mailings FROM (SELECT id FROM oto_sentmessages WHERE senderid = 1 AND subjecttext = 'Get More Traffic' AND FROM_UNIXTIME(timesent) >= '2023-01-01' UNION ALL SELECT id FROM oto_senthistory WHERE senderid = 1 AND subjecttext = 'Get More Traffic' AND FROM_UNIXTIME(timesent) >= '2023-01-01') AS combined;","exampleshow":1,"exampletitle":"Check your sent mailings"}]}