SQL – Sparse data in time series

[I wasn’t sure if this is the proper place to post SQL questions.] I have a public dataset of pharmaceutical prices. Any given drug gets a new price on some unpredictable day, and then the price remains that price until the next price change.


drug            date          new_price acetaminophen   2020-01-09    0.25 oxycontin       2020-01-10    1.40 valaxirin       2020-02-10    2.34 oranicin        2020-02-11    1.54 acetaminophen   2020-02-12    1.47 

I have to do a variety of analytics e.g. "what was the price of acetaminophen on 2020-02-01?" Well that would of course be 0.25, but I need a way to figure that out in SQL. I have a variety of more complex queries, e.g. "list the ten cheapest drugs on a given date". So a solution I think needs to be generalized.

I realize that one possible solution would be to run a job that populates the database with prices for every day of the year, but I prefer not to solve the problem that way.

Why has the Final Fantasy series largely changed for the worst (or JRPs/RPGs in general)?

From what many remembered as open-world, explorable, side-quest, challenging battles and tactics of many similar RPGS/JPRGs of the 90s to the 2000s even, it now largely seems like the genre — especially referencing to FF series since they are among the "top dogs" of it — have diminished. I get the impression that lots of what made the old games good is lost:

  1. What was once more explorable of a main navigation element seems to have become more centered, linear, and/or restrictive. You can have nicer walking animations and prettier backgrounds, but the same "tunnel" like forward direction — or more aimless all-way walking potential in huge open areas replaces that special emphasis on simple old rooms (often smaller) with less to give graphically but more to give in a travel, explorative or more sensible approach than just "hunting" or "running around and grinding." If you make a large area you should at least give different elements to it than just "lots of space." If you scale up you need more of that "something" to scale up too — otherwise it’s more empty.

  2. The old free-to-explore open-worlds/world maps, airship/flying ship/etc. mechanics (even re-visit mechanics) are almost always chopped down or implemented much less attractively (think how it started with FFX and then onwards — i.e. you can "explore" fast but it’s really largely watered down stuff/processes in doing such). The whole "open world" aspect to the classics is largely reduced to large areas/fields but no longer a blend of different terrains, sub-areas, sections or just the general nature/element of traveling/entering/exiting different areas rather than storyline/linear rules imposed on all areas/paths.

  3. The battle system is definitely a hot topic, as some will tell you the new mechanics add some new flesh to the table while others feel the older system worked best and it’s been "slaughtered" merely at the attempt of "spicing up" something that people already liked for the most part/settled in with over time. The thing is — if the battle system is to be made "better" so to speak — it should try and maintain the same elements of what the skeleton of original battle systems were based on. As an example old turn-based games kept the same skeleton even when becoming "active time" battles where it’s every turn to grab for themselves the quickest. The idea was that you can maintain the same "skeletal base" of the mechanics and only tweak them better — but lots of newer stuff almost always tries to go completely a new path that strays away from this with new experimentation, impositions, rules, and/or unneeded "extra steps" at times too. Basically it’s like the game’s old and functioning system has been put less concern to while trying to "splice" its old DNA under the impression that you can supposedly better an old thing by going in a completely a new "frankenstein" direction rather than just sprucing up the initial base in a more specific/oriented/targeted manner that fulfills its initial life blood/base than trying

  4. Always an extreme. Nowadays it seems games of this series are either too linear or not linear at all — there’s no longer a good balance between the two. For example one game may have so much explorable, massiveness to specific areas that you would be to get lost/tired/grinding excessively/etc. in one area to then go to the next one and rinse and repeat. On the other hand you can go super linear (think FFXIII for example) where everything is just "new area -> go straight -> battle -> story -> repeat" and such. What made the classics arguably more "wow" is the fact that the game — when it needs to — switches from storyline/linearity to open/some explorableness (to pique the natural exploring instinct) while going back to restriction when danger arose (defensive mechanism/protective inhibition) — because both of these angles match human behavior/etc. it suits gameplay. But if you make it either too open or too linear you force one side too long and it doesn’t align naturally with the cycle of human operability/engagement well enough to have proper "ups" and "downs."

  5. More "complex" systems or angles regarding leveling/power ups/etc. In old games it’s often fairly simple and straightforward to a large degree on how something more direct leads to a more expandable nature of said system to grow and keep delivering. What has replaced easy but expandable seems to be complex and rigid — more learning curves but less direction to go once you "have it." Slowly I think the series has gone this way, possibly starting with FFXIII/around that era. You make something simple that expands as needed become complex that really doesn’t give much over time. Something like junctioning in FF8 starts simple but can scale up to cool stuff as the game goes on, especially with the addition of GFs to character stats and so on. In a game like FFXIII for example you can liken the "powering system" to weak remnants of FFX and FFXII in ways of both combat means and stat growth.

  6. Games/scenes (probably applies to others outside this genre/series/etc. though) are now largely presented as cinematics/films with bits of gameplay as the only crux to break apart that concept of whether it’s innately a movie with gameplay or gameplay with cinematics (like older games of the series where "movies" in, say, the FMV form/class were much less emphasized as part of the overall game). "Cutscenes" in old FFs were mostly seamless or passive — now they are expected to emphasize more (due to the graphics) and "fill" a part of the game/impression as such rather than just be more of a seamless flow with only particular moments having more "weight" to them. In old FFs, how much of the story is lost removing the dialogue/locked moment/cutscenes? Now compare that to how much would be lost in modern games. If there is more to "lose" from the cutscenes overall then maybe they are relied on too much to shape the impression or experience of the game.

finding the combinatorial solutions of series and parallel nodes

I have n nodes, and I want to find the (non duplicate) number of possible ways in which these nodes can be combined in series and parallel, and also enumerate all the solutions. For example, for n=3, there are 19 possible combinations.

 0 (0, 1, 2)  1 (0, 2, 1)  2 (1, 2, 0)  3 (1, 0, 2)  4 (2, 0, 1)  5 (2, 1, 0)  6 [0, 1, 2]  7 [0, (1, 2)]  8 [0, (2, 1)]  9 (0, [1, 2]) 10 ([1, 2], 0) 11 [1, (0, 2)] 12 [1, (2, 0)] 13 (1, [0, 2]) 14 ([0, 2], 1) 15 [2, (0, 1)] 16 [2, (1, 0)] 17 (2, [0, 1]) 18 ([0, 1], 2) 

In the notation above, a series combination is denoted by (..) and a parallel combination is denoted by [..]. Duplicates are removed, for example [0,1,2] is the same as [1,2,0] since all of them are happening in parallel so the order does not matter here.

Can you give me an algorithm for this, or if any such algorithm already exists, then point me to it?

(I tried googling for a solution, but did not hit any relevant answer, maybe I was entering the wrong keywords.)

Note: for a sequential-only solution, the answer is easy, it is n!, and the enumeration of the solutions is also easy. But when parallelism (especially non duplicates) is added to the problem, it gets very complex.

Approximate solution of a nonlinear ODE in the form of a Fourier series containing the coefficients of the initial ODE

In this topic we considering nonlinear ODE:

$ \frac{dx}{dt}= (x^4) \cdot a_1 \cdot sin(\omega_1 \cdot t)-a_1 \cdot sin(\omega_1 \cdot t + \frac{\pi}{2})$ – Chini ODE

And system of nonlinears ODE:

$ \frac{dx}{dt}= (x^4+y^4) \cdot a_1 \cdot sin(\omega_1 \cdot t)-a_1 \cdot sin(\omega_1 \cdot t + \frac{\pi}{2})$

$ \frac{dy}{dt}= (x^4+y^4) \cdot a_2 \cdot sin(\omega_2 \cdot t)-a_2 \cdot sin(\omega_2 \cdot t + \frac{\pi}{2})$

Chini ODE’s NDSolve in Mathematica:

pars = {a1 = 0.25, \[Omega]1 = 1} sol1 = NDSolve[{x'[t] == (x[t]^4) a1 Sin[\[Omega]1 t] - a1 Cos[\[Omega]1 t], x[0] == 1}, {x}, {t, 0, 200}] Plot[Evaluate[x[t] /. sol1], {t, 0, 200}, PlotRange -> Full] 

System of Chini ODE’s NDSolve in Mathematica:

pars = {a1 = 0.25, \[Omega]1 = 3, a2 = 0.2, \[Omega]2 = 4} sol2 = NDSolve[{x'[t] == (x[t]^4 + y[t]^4) a1 Sin[\[Omega]1 t] - a1 Cos[\[Omega]1 t], y'[t] == (x[t]^4 + y[t]^4) a2 Sin[\[Omega]2 t] - a2 Cos[\[Omega]2 t], x[0] == 1, y[0] == -1}, {x, y}, {t, 0, 250}] Plot[Evaluate[{x[t], y[t]} /. sol2], {t, 0, 250}, PlotRange -> Full] 

There is no exact solution to these equations, therefore, the task is to obtain an approximate solution.

Using AsymptoticDSolveValue was ineffective, because the solution is not expanded anywhere except point 0.

The numerical solution contains a strong periodic component; moreover, it is necessary to evaluate the oscillation parameters. Earlier, we solved this problem with some users as numerically: Estimation of parameters of limit cycles for systems of high-order differential equations (n> = 3)

How to approximate the solution of the equation by the Fourier series so that it contains the parameters of the original differential equation in symbolic form, namely $ a_1$ , $ \omega_1$ , $ a_2$ and $ \omega_2$ .

Why doesn’t Mathematica provide an answer while Wolfram|Alpha does, concerning a series convergence?

Among other series I’ve been working on, I was asked to find whether $ $ \sum_n 1-\cos(\frac{\pi}{n})$ $ converged, and Mathematica’s output to SumConvergence[1 - Cos[Pi/n], n] simply was repeating the input, without further information. Wolfram|Alpha, though, at least told me which test were or not conclusive.

I’m new to Mathematica, and even though I’ve looked both on Google and into Wolfram’s documentation, I haven’t found information that could help me figure out how to get, from Mathematica, the conditions for the convergence of a series involving something else than powers of a variable.

I would appreciate if you could give me some clues on the typical procedure to make Mathematica correctly evaluate the convergence of a series, or/and to return the conditions for convergence. Thank you in advance.

Is the “Immortals Handbook” series official WotC material?

Regardless of any thoughts on the general quality, as sources seem conflicted on that, my question only relates to how official these books are. How valid should I consider them? Are they actual WotC material? Are they acknowledged by WotC? Or are they wholly 3rd party? Also, for what edition are they intended anyway?

The series I am referring to includes this book.

Selling a novel/manga site for a specific series

Why are you selling this site?
New projects that need $ $ $ $

How is it monetized?
Adsense. Monthly revenue about $ 80-$ 100. The revenue is still growing very quickly as the traffic is booming.

Does this site come with any social media accounts?

How much time does this site take to run?
1-2h every week.

What challenges are there with running this site?
None. Anyone can do this because the work is very simple to do.

Some other stats (pm me for…

Selling a novel/manga site for a specific series

PostgreSQL – Generate multiple running totals w/ group by day (Generate Series)

I’m having trouble figuring out a way to make this query work.

I have a set of transactions of stock purchases by users and I want to keep track of a running balance of each stock as the year progresses. I am using a windowing function to track the running balance but for some reason I cannot get the GROUP BY portion of this query to work.

It continues to have duplicate days in the result set even when I attempt to group by the date (created_at). Sample below:

select   t.customer_id,   t.created_at::date,   sum(case when t.stock_ticker = 'tsla' then t.amount end) over (order by t.created_at::date rows unbounded preceding) as tsla_running_amount,   sum(case when t.stock_ticker = 'goog' then t.amount end) over (order by t.created_at::date rows unbounded preceding) as goog_running_amount, from transactions t group by t.created_at, t.customer_id, t.stock_ticker, t.amount order by t.created_at desc; 

The results here always comes back with multiple records per day, when I want them to be grouped all into one day.

After doing some research I attempted cast the “created_at” to ::date inside of the group by fuction as well, but I get a:

“Column t.created_at must appear in the GROUP BY clause or be used in an aggregate function” error every single time.”

In addition, the results are only going to show the day’s in which a transaction has happened for a user. I need to be able to show a ROW for each day in a time series (1 year) even if the user did not make a transaction on that day. (Using the most recent running balance on the row instead)

I think that using “generate_series” is the proper way to do this, but I am having trouble understanding how to fit it in.

Thank you in advance!