SQL select only last entry of date from a calculated field

Related searches

Hey I'm trying to visualize a past 30 days trend as a line graph in metabase and therefore have to query the data in a right way.

The table looks like this:

    DATE                          SCORE
September 7, 2019, 12:00 AM         3
September 7, 2019, 12:00 AM         4
September 8, 2019, 12:00 AM         9
September 9, 2019, 12:00 AM         10

I tried this query which calculates the NPS and the rolling sum over the last 30 rows.

select DATE, 
        CASE
            WHEN SCORE <= 6 THEN '-1'
            WHEN SCORE <= 8 THEN '0'
            WHEN SCORE > 8 THEN '1'
        END AS NPS
        ,SUM(NPS) OVER(ORDER BY DATE ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) AS NPSTotal
FROM Table;

Which looks like this

    DATE                          NPS    NPSTotal
September 7, 2019, 12:00 AM         -1    -1
September 7, 2019, 12:00 AM         -1    -2
September 8, 2019, 12:00 AM         1     -1
September 9, 2019, 12:00 AM         1      0

However, if I want to plot the results in metabase and put the date on the x-axis it naturally aggregates the NPSTotal, giving me a -3 for the 7 September instead of -2. I think the result of my query should look like this.

    DATE                        NPSTotal
September 7, 2019, 12:00 AM       -2
September 8, 2019, 12:00 AM       -1
September 9, 2019, 12:00 AM        0 

So I need two things: 1. Sum the NPS over the date of the column and all votes from the last 30 days instead of rows. 2. The output should give out only one NPSTotal entry per date, which should be the last value.

Would be happy if somebody has a solution for this.


If you want one row per date, then use aggregation. That would seem to be:

SELECT DATE, MIN(NPSTotal) as NPSTotal
FROM (SELECT DATE, 
             (CASE WHEN SCORE <= 6 THEN '-1'
                   WHEN SCORE <= 8 THEN '0'
                   WHEN SCORE > 8 THEN '1'
              END) AS NPS,
             SUM(NPS) OVER (ORDER BY DATE ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) AS NPSTotal
     FROM Table
    ) t
GROUP BY DATE;

It is unclear how to resolve ties in your data. This uses MIN().

How to Select Last Entry If None On Input Date SQL, The WHERE clause limits the rows to dates up to the input date given (with that that row, assuming there are no duplicates in the DATE_TIME column). the WHERE clause is evaluated before the ranks can be calculated in� SELECT T1.* FROM transactions T1 WHERE T1.date = ( SELECT MAX(date) FROM transactions T2 WHERE T1.student_name = T2.student_name ); The above query works in both MySQL and SQL Server and gives the following result. LAST RECORD USING ROW_NUMBER FUNCTION. We can also fetch the last record using the ROW_NUMBER function. Like the MAX function ROW_NUMBER can also give us the last record of each group.


This is how we calculate nps and bucket by date. I'm assuming NPS is net promoter score. I'd tend to calculate the overall NPS in one go then plot the actual value rather than the relative daily values because it also depends on how many people are responding. If you have one responder on day 1 and 100 responders on day 2, then the single responder on day 1 will skew the NPS.

select date(scored_at),
       ((sum(if(score>8,1,0))/count(*))-(sum(if(score<7,1,0))/count(*)))*100 as nps
from nps_scores
where score is not null
and [date comparators can go here]
group by date(scored_At)
;

Finding the Oldest/Youngest Records Within a Group, This is straightforward in SQL. Oldest or Most Recent records relative to another field And let's say you need to display a list of users next to the date of their price and quantity or maybe you need to perform a calculation on them. The key idea is that you select only the orders that match the user_id� The last () function is used to return the last value of the specified column. Syntax for SQL SELECT LAST () FUNCTION: SELECT LAST (column_name) FROM table_name; You should note that the last () function is only supported in MS Access.


Here's the code I use in Mode to show NPS over many window sizes, over the last while but also only show windows where the start of the window is after the time point where we start collecting data.

WITH scores AS (
  SELECT score, date_Created::date AS date
  FROM db_name.nps.survey_answers
), days AS (
    SELECT column1 AS days FROM (values (1),(14),(35),(91),(105))
), wins_end AS (
    SELECT dateadd('day',ROW_NUMBER() over (order by SEQ8()),current_date) as win_date_end
    FROM table(generator(rowcount => 365*2.5))
), wins AS (
    SELECT d.days
        ,w.win_date_end
        ,dateadd('day',-d.days, w.win_date_end) AS win_date_start
    FROM days AS d
    JOIN wins_end AS w
)
SELECT w.days::text AS days
    ,w.win_date_end
    ,w.win_date_start
    ,AVG(score) AS a_score
    ,SUM(iff(score > 8,1,0)) AS promoter
    ,SUM(iff(score = 7 or score = 8,1,0)) AS pASsive
    ,SUM(iff(score < 7,1,0)) AS detractor
    ,COUNT(*) AS total
    ,ROUND((100*promoter/total)-(100*detractor/total),1) AS nps  
FROM scores AS s
JOIN wins w ON s.date >= w.win_date_start AND s.date <= w.win_date_end
GROUP BY 1,2,3
HAVING win_date_start >= '2017-06-01'

This gives to a sliding window start/end for each window size for the last 2.5 years (that bit could be made simpler) and the calculates the NPS for the window against the data set.

Find records with the most recent or oldest dates, A totals query is a select query that uses Sum, First, and Last to calculate values for a given field. only the field that contains your group or� These two queries rely on the assumption that Taco_value always increases over time.;WITH x AS ( SELECT Taco_ID, Taco_date, dr = ROW_NUMBER() OVER (PARTITION BY Taco_ID, Taco_Value ORDER BY Taco_date), qr = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date) FROM dbo.Taco ), y AS ( SELECT Taco_ID, Taco_date, rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID, dr ORDER BY qr DESC) FROM x


Calculate The Last Or Max Value Within A Table Of Data Using DAX , in many situations to extract very interesting insights from your data. Good luck. Sam Duration: 9:28 Posted: Jun 19, 2019 @user1671639 the datetime data type always contains both a date and a time, you can't sensibly store one without the other - unless you're using SQL Server 2008, in which case there are also separate 'date' and 'time' data types.


Only measuring the "latest" values - Modeling, A dozen campaigns with hundreds of dates and thousands of vists? Then, with this calculated field, using the MAX aggregate function gets us the latest row for each inventory_snapshot_dynamic{ derived_table:{ sql: SELECT inventory_id, � The prod_id field is aliased as id and the calculated field prod_price*.8 is aliased as discount_price. Exercise 8: Write a db-less SELECT query in the mysql shell that uses a calculated field returning the greatest of three numbers using the GREATEST() function. Ensure the calculated field has an appropriately named alias. Review problems


Here's another solution to retrieving the records only with a field that has the maximum value for that field. This works for SQL400 which is the platform I work on. In this example, the records with the maximum value in field FIELD5 will be retrieved by the following SQL statement.