« first day (1306 days earlier)      last day (802 days later) » 

Buraian
Buraian
8:56 AM
Hello friends, can someone help me with this query:

>I want to know, out of all the users who joined MSE in a year, what fraction of users gained the privilege of becoming an established user relative to total user joined (1k rep) plotted as a function of time (unit=year).
 
 
1 hour later…
Glorfindel
Glorfindel
10:16 AM
@Buraian it's difficult, since historic reputation information is not kept and you'll need ugly queries like this one to generate it from events (votes, bounties, etc.)
That kind of queries don't scale well to the entire user base of Math.SE
Maybe if you just concentrate on votes and don't pay attention to the reputation cap you can get a good approximation.
 
Martin
Martin
10:32 AM
I will add that the same thing was briefly discussed in another room: chat.stackexchange.com/transcript/19138/2022/2/23
But certainly this room is a better place for getting attention of some SQL-experts.
 
Martin
Martin
10:56 AM
Just to have a rough idea, here is a comparison showing differences between actual reputation and reputation calculated just from the upvotes/downvotes/accepts: MathOverflow, Math.
 
 
4 hours later…
Martin
Martin
2:41 PM
I tried at least calculation how much reputation users got in the first year: MathOverflow, Math.
Or the first month: MathOverflow, Math
As mentioned about, I am only using votes (ignoring daily reputation cap and many other things). But still, I was surprised that the query does no time out on Mathematics - that is a rather big site. (I thought I'll need to make some restriction on the users.)
Maybe this could be explained by the daily reputation cap, but the results I am getting are quite large. For example, Emerton has 4410 reputation points in my "month" query and 3780 according to the reputation league.
 
Martin
Martin
2:59 PM
In any case, if we're willing to take this as a (rather rough) approximation of reputation - and if we assume that I did not make some stupid mistake when writing those queries - this could be more-or-less in the direction of what @Buraian was asking about: MathOverflow, ...
... Math.
 
 
1 hour later…
Martin
Martin
4:01 PM
BTW there certainly are some high rep users who have deleted their accounts on any site: MathOverflow, Math.
But if we would like a similar query that includes the deleted users, that would complicate things. For example, we no longer can use Users.CreationDate - for deleted users we don't have this information. Maybe the first post could be an approximation?
 
 
4 hours later…
Feeds
Feeds
7:43 PM
0
Q: PSQL: delete using join and return values

GabrielHello I have this query: DELETE FROM c_table as c USING a_table as a, b_table as b WHERE a.id = c.id AND b.a_id = c.id RETURNING a.*, b.*; but I have a problem, I need all the rows of table B that have the a_id, using this query I'm just returning...

support data-explorer database
 
Buraian
Buraian
8:10 PM
@Glorfindel Hi! Could you please provide me an SDE of this: On Mathematics meta, what is the distribution of number of comments, answers, votes posted as a function of account age? I have a hunch that the meta at the moment is dominated by accounts older than 5 years (which I believe is a bad sign), to justify/ test it, this sede would be helpful.
 
Buraian
Buraian
8:23 PM
 
Glorfindel
Glorfindel
8:49 PM
@Buraian Hi! I might give it a try later (and let you know on the Math Meta post) but I'm rather busy right now.
 

« first day (1306 days earlier)      last day (802 days later) »